动态SQL结果在SQL存储过程中生成临时表。

时间:2021-08-05 20:12:10

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.

问题是我想调用不同的过程来返回不同的列。因此,我必须笼统地定义表#T1。但我不知道怎么做。

Can anyone help me on this problem?

有人能在这个问题上帮助我吗?

8 个解决方案

#1


33  

Try:

试一试:

SELECT into #T1 execute ('execute ' + @SQLString )

And this smells real bad like an sql injection vulnerability.

这闻起来很糟糕,就像sql注入漏洞一样。


correction (per @CarpeDiem's comment):

每@CarpeDiem校正(评论):

INSERT into #T1 execute ('execute ' + @SQLString )

also, omit the 'execute' if the sql string is something other than a procedure

此外,如果sql字符串不是过程,则省略“execute”

#2


31  

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:

将返回错误“无效的对象名称#T1”。这是因为临时表#T1是在比执行代码块更低的级别上创建的。为了修复,请使用一个全局临时表:

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

希望这有助于,杰西

#3


14  

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...

请注意全局临时表解决方案,因为如果两个用户同时使用相同的例程,那么这可能会失败,因为所有用户都可以看到全局临时表……

#4


8  

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

动态地使用名称中的GUID创建一个全局临时表。然后,您可以通过dyn sql在您的代码中使用它,而不必担心另一个调用相同sproc的进程将使用它。当您不知道在每次运行时从底层选择的表中期望什么时,这是很有用的,因此您不能预先显式地创建一个临时表。你需要在语法中使用SELECT *

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 

#5


7  

INSERT INTO #TempTable
EXEC(@SelectStatement)

#6


0  

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.

我不确定我是否理解得很好,但是也许你可以在字符串中形成CREATE语句,然后执行那个字符串?这样你可以添加任意多的列。

#7


0  

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

#8


0  

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

#1


33  

Try:

试一试:

SELECT into #T1 execute ('execute ' + @SQLString )

And this smells real bad like an sql injection vulnerability.

这闻起来很糟糕,就像sql注入漏洞一样。


correction (per @CarpeDiem's comment):

每@CarpeDiem校正(评论):

INSERT into #T1 execute ('execute ' + @SQLString )

also, omit the 'execute' if the sql string is something other than a procedure

此外,如果sql字符串不是过程,则省略“execute”

#2


31  

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:

将返回错误“无效的对象名称#T1”。这是因为临时表#T1是在比执行代码块更低的级别上创建的。为了修复,请使用一个全局临时表:

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

希望这有助于,杰西

#3


14  

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...

请注意全局临时表解决方案,因为如果两个用户同时使用相同的例程,那么这可能会失败,因为所有用户都可以看到全局临时表……

#4


8  

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

动态地使用名称中的GUID创建一个全局临时表。然后,您可以通过dyn sql在您的代码中使用它,而不必担心另一个调用相同sproc的进程将使用它。当您不知道在每次运行时从底层选择的表中期望什么时,这是很有用的,因此您不能预先显式地创建一个临时表。你需要在语法中使用SELECT *

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 

#5


7  

INSERT INTO #TempTable
EXEC(@SelectStatement)

#6


0  

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.

我不确定我是否理解得很好,但是也许你可以在字符串中形成CREATE语句,然后执行那个字符串?这样你可以添加任意多的列。

#7


0  

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

#8


0  

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