Dynamic SQL results into temp table in SQL Stored procedure

Dhanapal_53262 0
Viewed 203340

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?

dynamic-sql tsql sql-server
9 Answers

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

Joel_Coehoorn_30430

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

user_1220730

Try Below code for creating temp table dynamically from Stored Procedure Output using T-SQL

declare @ExecutionName varchar(1000) = 'exec [spname] param1,param2 '
declare @sqlStr varchar(max) = ''

   declare @tempTableDef nvarchar(max) =   
  (  
  SELECT distinct   
   STUFF(  
    (  
     SELECT ','+a.[name]+' '+[system_type_name]  
  +'  
   ' AS [text()]  
     FROM sys.dm_exec_describe_first_result_set  (@ExecutionName, null, 0) a  
     ORDER BY a.column_ordinal  
     FOR XML PATH ('')  
    ), 1, 1, '') tempTableDef   

  FROM sys.dm_exec_describe_first_result_set  (@ExecutionName, null, 0) b  
  )  

  IF ISNULL(@tempTableDef ,'') = '' RAISERROR( 'Invalid SP Configuration. At least one column is required in Select list of SP output.',16,1) ;    
                                      
  set @tempTableDef='CREATE TABLE #ResultDef   
  (  
  ' + REPLACE(@tempTableDef,'
','') +'  
  )  

  INSERT INTO #ResultDef   
  ' + @ExecutionName    
          
  Select  @sqlStr  = @tempTableDef +' Select * from  #ResultDef '   
exec(@sqlStr)
user_108667440 
DECLARE @EmpGroup INT =3 ,
        @IsActive BIT=1

DECLARE @tblEmpMaster AS TABLE
        (EmpCode VARCHAR(20),EmpName VARCHAR(50),EmpAddress VARCHAR(500))

INSERT INTO @tblEmpMaster EXECUTE SPGetEmpList @EmpGroup,@IsActive

SELECT * FROM @tblEmpMaster
user_13049350

Not sure if I understand well, but maybe you could form the CREATE statement inside a string, then execute that String? That way you could add as many columns as you want.

user_765830
Related