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?
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
Invalid object name '#T1'. and you cannot create #T1 in advance because, as OP mentioned, you don't know the columnsYou 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
Be careful of a global temp table solution as this may fail if two users use the same routine at the same time as a global temp table can be seen by all users...
create a global temp table with a GUID in the name dynamically. Then you can work with it in your code, via dyn sql, without worry that another process calling same sproc will use it. This is useful when you dont know what to expect from the underlying selected table each time it runs so you cannot created a temp table explicitly beforehand. ie - you need to use SELECT * INTO syntax
DECLARE @TmpGlobalTable varchar(255) = 'SomeText_' + convert(varchar(36),NEWID())
-- select @TmpGlobalTable
-- build query
SET @Sql =
'SELECT * INTO [##' + @TmpGlobalTable + '] FROM SomeTable'
EXEC (@Sql)
EXEC ('SELECT * FROM [##' + @TmpGlobalTable + '] ')
EXEC ('DROP TABLE [##' + @TmpGlobalTable + ']')
PRINT 'Dropped Table ' + @TmpGlobalTable
INSERT INTO #TempTable
EXEC(@SelectStatement)
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)
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
CREATE PROCEDURE dbo.pdpd_DynamicCall
AS
DECLARE @SQLString_2 NVARCHAR(4000)
SET NOCOUNT ON
Begin
--- Create global temp table
CREATE TABLE ##T1 ( column_1 varchar(10) , column_2 varchar(100) )
SELECT @SQLString_2 = 'INSERT INTO ##T1( column_1, column_2) SELECT column_1 = "123", column_2 = "MUHAMMAD IMRON"'
SELECT @SQLString_2 = REPLACE(@SQLString_2, '"', '''')
EXEC SP_EXECUTESQL @SQLString_2
--- Test Display records
SELECT * FROM ##T1
--- Drop global temp table
IF OBJECT_ID('tempdb..##T1','u') IS NOT NULL
DROP TABLE ##T1
End
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.
