我们可以在存储过程中使用temproray表，其中包含动态sql。 [英] can we use temproray tables in stored procedure which is having dynamic sql in it.

问题描述

嗨
最近我试图在动态sql之前创建一个临时表，然后我编译了SP。

当我试图执行SP时它给出的错误就像我的临时表doen不存在

ex：

Hi Recently i tried to create one temp table before dynamic sql and i compiled the SP.
when i tried to execute the SP it is giving the error like my temp table doen''t exist
ex:

PROCEDURE [dbo].[Proc_test]
as
begin
DECLARE @sqlstring NVARCHAR(4000)
select * into #test from xtable
SET @sqlstring='select * from #test'
end

现在它编译正常，但在执行时它显示错误，如

#test不存在

now it is compiling fine but at the time of executing it is showing the error like
#test doesn''t exist

推荐答案

临时表的范围仅限于在动态查询中定义的动态查询。替代选项 - 您可以使用全球临时表 [ ^ ]或在动态查询之外创建临时表。请查看以下链接以获取更多信息

http://blog.shkedy.com/2007/01/temporary-tables-and-dynamic-sql.html [ ^ ]

The scope of the temp table is limited to the dynamic query when its defined within the dynamic query. Alternate options are - you can use Global temporary table[^] or create the temp table outside your dynamic query. Take a look at the below link for more information
http://blog.shkedy.com/2007/01/temporary-tables-and-dynamic-sql.html[^]

这篇关于我们可以在存储过程中使用temproray表，其中包含动态sql。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！