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