Menu
The code is as follows:
ALTER PROCEDURE dbo.pdpd_DynamicCall @SQLString varchar(4096) = null AS Begin create TABLE #T1 ( column_1 varchar(10) , column_2 varchar(100) ) insert into #T1 execute ('execute ' + @SQLString ) select * from #T1 End The problem is that I want to call different procedures that can give back different columns. Therefore I would have to define the table #T1 generically. But I don't know how.
Can anyone help me on this problem?
654
When the stored procedure returns a lot of columns and you do not want to manually "create" a temporary table to hold the result, I've found the easiest way is to go into the stored procedure and add an "into" clause on the last select statement and add 1=0 to the where clause.
Using dynamic SQL inside stored procedures This stored procedure is used to search for products based on different columns like name, color, productid, and the product number. The dynamic SQL statement is constructed based on the input parameters passed to the stored procedure and is executed by the EXEC command.
set @cmd = ' SELECT * into #temp3 from ( select * from sometable ) x pivot ( max(buildrate) for name in ('+ @columns +') ) as y ' execute(@cmd); select * from #temp3 left join performed in an elegant way...
Try:
SELECT into #T1 execute ('execute ' + @SQLString ) And this smells real bad like an sql injection vulnerability.
correction (per @CarpeDiem's comment):
INSERT into #T1 execute ('execute ' + @SQLString ) also, omit the 'execute' if the sql string is something other than a procedure
108
You can define a table dynamically just as you are inserting into it dynamically, but the problem is with the scope of temp tables. For example, this code:
DECLARE @sql varchar(max) SET @sql = 'CREATE TABLE #T1 (Col1 varchar(20))' EXEC(@sql) INSERT INTO #T1 (Col1) VALUES ('This will not work.') SELECT * FROM #T1 will return with the error "Invalid object name '#T1'." This is because the temp table #T1 is created at a "lower level" than the block of executing code. In order to fix, use a global temp table:
DECLARE @sql varchar(max) SET @sql = 'CREATE TABLE ##T1 (Col1 varchar(20))' EXEC(@sql) INSERT INTO ##T1 (Col1) VALUES ('This will work.') SELECT * FROM ##T1 Hope this helps, Jesse
32
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
