dynamic use of stored procedure resultset

Hello,

I have a stored procedur like this:

--------------
ALTER PROCEDURE dbo.pdpd_DynamicCall
@.SQLString varchar(4096) = null
AS

create TABLE #T1
( column_1 varchar(10) ,
column_2 varchar(100) )

insert into #T1
execute ('execute ' + @.SQLString )

select * from #T1
--------------

The problem is that I want to call different procedures that can give back different columns.
Therefor I would have to define the table #T1 generically.But I don't know how.
Can anyone help me on this problem?

thank you
Werneryou do not need the creation of the temporary table.

ALTER PROCEDURE dbo.pdpd_DynamicCall
@.SQLString varchar(4096) = null
AS

execute ('execute ' + @.SQLString )

this solves your problem but this is the most useless SP ever and your going to have stuff like cached execution plans that do not match the query you are executing.|||This sproc is very dangerous. Whoever has rights to execute it has right to execute arbitrary sql scripts, such as "truncate table AllMyCustomers". Do you really want that?

You are asking for trouble if you put this in a production system.|||Hello,

I have a stored procedur like this:

--------------
ALTER PROCEDURE dbo.pdpd_DynamicCall
@.SQLString varchar(4096) = null
AS

create TABLE #T1
( column_1 varchar(10) ,
column_2 varchar(100) )

insert into #T1
execute ('execute ' + @.SQLString )

select * from #T1
--------------

The problem is that I want to call different procedures that can give back different columns.
Therefor I would have to define the table #T1 generically.But I don't know how.
Can anyone help me on this problem?

thank you
Werner
Like jezemine says - dynamic SQL needs to be encapsulated in a very controlled fashion.

I have a similar situation where our "configurator" actually has column names to define a mapping between Inventory and Sales. Long story.

We offer the column names on a drop-down list.

The Stored Procedure that does the implementation is passed column names in various positions. Those column names are a result of a drop-down box (so the can't just formulate their own SQL scripting), and they are of limited size (ie: only big enough for a reasonable column name - like 40 characters).

For your case; if you had a list of column names, types, and sizes rather than the full SQL statement, you could use them to build your temp table.

I have never tried passing an array (or collection) as an SP_ parameter, but that would be ideal if you have an unknown number of columns.

You could also first do some parsing verify that they are valid column names (no spaces or punctuation) to further ensure they aren't passing in DLL commands like "truncate table ...".

So your Stored Procedure would supply all SQL keywords and restrict any from being passed.|||Thank you all for the detailled help!!
Especially the security aspect is a part I have to rethink.

best regards
Werner

Dynamic SQL or CASE statement?

hi all,
we are having an asp app that is fetching a big resultset to display in a
list screen. The screen has 4 or 5 columns which have the click sort option
(ie., on clicking the column hdr, the list needs to be sorted out).
Currently, we are using a procedure which would dynamically construct the
sort order and use exec(@.stmt) to send back the resultset. Coz of perf issue
s
of late, we are thinking of removing dyn sql in the proc and recreating it
using CASE/IF statements. Which option would be better in general? Currently
,
the dyn sql has high counts of CPU.
would appreciate any help in this regards
thx
paraaWhy sort it at all? Can't ASP handle that? It would be more scalable than
making SQL Server do all the sorting.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"paraa" <paraa@.discussions.microsoft.com> wrote in message
news:6D3D38EC-AAB8-4BF0-A74F-AAE898D73BBD@.microsoft.com...
hi all,
we are having an asp app that is fetching a big resultset to display in a
list screen. The screen has 4 or 5 columns which have the click sort option
(ie., on clicking the column hdr, the list needs to be sorted out).
Currently, we are using a procedure which would dynamically construct the
sort order and use exec(@.stmt) to send back the resultset. Coz of perf
issues
of late, we are thinking of removing dyn sql in the proc and recreating it
using CASE/IF statements. Which option would be better in general?
Currently,
the dyn sql has high counts of CPU.
would appreciate any help in this regards
thx
paraa|||tom,
we have lean clients and so after lot of brainstorming have put the sorting
on the server side..sorting on client is not an option over here..as per the
scalability factor - may not be that much of an issue..thatz why we are
thinking abt using CASE on the proc side...wanted to know if that would buy
us something vis a vis dyn sql
thx
"Tom Moreau" wrote:

> Why sort it at all? Can't ASP handle that? It would be more scalable tha
n
> making SQL Server do all the sorting.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "paraa" <paraa@.discussions.microsoft.com> wrote in message
> news:6D3D38EC-AAB8-4BF0-A74F-AAE898D73BBD@.microsoft.com...
> hi all,
> we are having an asp app that is fetching a big resultset to display in a
> list screen. The screen has 4 or 5 columns which have the click sort optio
n
> (ie., on clicking the column hdr, the list needs to be sorted out).
> Currently, we are using a procedure which would dynamically construct the
> sort order and use exec(@.stmt) to send back the resultset. Coz of perf
> issues
> of late, we are thinking of removing dyn sql in the proc and recreating it
> using CASE/IF statements. Which option would be better in general?
> Currently,
> the dyn sql has high counts of CPU.
> would appreciate any help in this regards
> thx
> paraa
>|||How "lean" are we talking? I find it hard to believe that an IIS server
can't handle this.
That said, you can do dynamic sorting without resorting to dynamic SQL:
declare @.sort varchar (100)
set @.sort = 'Country'
select
*
from
dbo.Customers
order by
case when @.sort = 'Country' then Country end
, case when @.sort = 'CustomerID' then CustomerID end
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"paraa" <paraa@.discussions.microsoft.com> wrote in message
news:C3B84B96-3839-4A66-8FAE-0F737FBE21C5@.microsoft.com...
tom,
we have lean clients and so after lot of brainstorming have put the sorting
on the server side..sorting on client is not an option over here..as per the
scalability factor - may not be that much of an issue..thatz why we are
thinking abt using CASE on the proc side...wanted to know if that would buy
us something vis a vis dyn sql
thx
"Tom Moreau" wrote:

> Why sort it at all? Can't ASP handle that? It would be more scalable
> than
> making SQL Server do all the sorting.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "paraa" <paraa@.discussions.microsoft.com> wrote in message
> news:6D3D38EC-AAB8-4BF0-A74F-AAE898D73BBD@.microsoft.com...
> hi all,
> we are having an asp app that is fetching a big resultset to display in a
> list screen. The screen has 4 or 5 columns which have the click sort
> option
> (ie., on clicking the column hdr, the list needs to be sorted out).
> Currently, we are using a procedure which would dynamically construct the
> sort order and use exec(@.stmt) to send back the resultset. Coz of perf
> issues
> of late, we are thinking of removing dyn sql in the proc and recreating it
> using CASE/IF statements. Which option would be better in general?
> Currently,
> the dyn sql has high counts of CPU.
> would appreciate any help in this regards
> thx
> paraa
>|||If you simply need to sort by a single column, then the ASP.NET DataGrid
could handle this. However, if your sort requirements are more complex
involving multiple columns or complex conditions, then it would probably
need to be done within the stored procedure. A conditional [order by] clause
using a case expression would be much preferable than dynamic SQL. Just as a
side note, you can also include case expressions in the [where] clause as
well for conditional row filtering, or even in the [group by] clause for
conditional grouping.
In the example below, there are 3 different sort options enabled using a
parameter called @.sort :
. . .
order by
case @.sort
when 0 then NULL
when 1 then company
when 2 then region
end,
case @.sort
when 0 then accountno
when 1 then lastname
when 2 then lastname
end
"paraa" <paraa@.discussions.microsoft.com> wrote in message
news:6D3D38EC-AAB8-4BF0-A74F-AAE898D73BBD@.microsoft.com...
> hi all,
> we are having an asp app that is fetching a big resultset to display in a
> list screen. The screen has 4 or 5 columns which have the click sort
> option
> (ie., on clicking the column hdr, the list needs to be sorted out).
> Currently, we are using a procedure which would dynamically construct the
> sort order and use exec(@.stmt) to send back the resultset. Coz of perf
> issues
> of late, we are thinking of removing dyn sql in the proc and recreating it
> using CASE/IF statements. Which option would be better in general?
> Currently,
> the dyn sql has high counts of CPU.
> would appreciate any help in this regards
> thx
> paraa|||thx a bunch JT and tom..thatz what i was arriving at when i mentioned CASE
stmt v/s dyn sql...just wanted to make sure with the xperts
"JT" wrote:

> If you simply need to sort by a single column, then the ASP.NET DataGrid
> could handle this. However, if your sort requirements are more complex
> involving multiple columns or complex conditions, then it would probably
> need to be done within the stored procedure. A conditional [order by] clau
se
> using a case expression would be much preferable than dynamic SQL. Just as
a
> side note, you can also include case expressions in the [where] clause as
> well for conditional row filtering, or even in the [group by] clause for
> conditional grouping.
> In the example below, there are 3 different sort options enabled using a
> parameter called @.sort :
> .. . .
> order by
> case @.sort
> when 0 then NULL
> when 1 then company
> when 2 then region
> end,
> case @.sort
> when 0 then accountno
> when 1 then lastname
> when 2 then lastname
> end
> "paraa" <paraa@.discussions.microsoft.com> wrote in message
> news:6D3D38EC-AAB8-4BF0-A74F-AAE898D73BBD@.microsoft.com...
>
>