从 SQL 存储过程返回临时表
我编写了这个存储过程：
USE [FAB28]
GO
/****** Object: StoredProcedure [dbo].[spPLCIOFilter2] Script Date: 12/06/2011 08:00:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[spPLCIOFilter2]
(
@filter1 VARCHAR(50) = '%',
@filter2 VARCHAR(50) = '%',
@slot VARCHAR(50) = '%'
)
AS
BEGIN
CREATE TABLE #temp
(
"PLC RIO" char(20)
,SIGNAL varchar(6)
,RACK char(2)
,SLOT char(2)
,POINT char(2)
,"CARD" char(20)
,TAG char(30)
,PROJ char(10)
,"DESCRIPTION" char(100)
,"ADDRESS" varchar(22)
)
INSERT INTO #temp
SELECT
[PLC] "PLC RIO"
,[SIGNAL] SIGNAL
,[RACK] RACK
,[SLOT] SLOT
,[POINT] POINT
,[CARD] "CARD"
,[TAG] TAG
,[PROJ] PROJ
,[DESCRIPTION] "DESCRIPTION"
,[ADDRESS] "ADDRESS"
FROM [FAB28].[dbo].[PLC_TAGS2]
WHERE [PLC] <> ''
and [PLC] LIKE '%' + @filter1 + '%'
and [PLC] LIKE '%' + @filter2 + '%'
and [SLOT] LIKE '%' + @slot + '%'
ORDER BY [PLC],[RACK],[SLOT],[POINT]
END
我想返回临时表#temp，但return #temp不起作用。我还希望临时表将在函数末尾被删除。
我该怎么做呢？
I wrote this stored procedure:
USE [FAB28]
GO
/****** Object: StoredProcedure [dbo].[spPLCIOFilter2] Script Date: 12/06/2011 08:00:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[spPLCIOFilter2]
(
@filter1 VARCHAR(50) = '%',
@filter2 VARCHAR(50) = '%',
@slot VARCHAR(50) = '%'
)
AS
BEGIN
CREATE TABLE #temp
(
"PLC RIO" char(20)
,SIGNAL varchar(6)
,RACK char(2)
,SLOT char(2)
,POINT char(2)
,"CARD" char(20)
,TAG char(30)
,PROJ char(10)
,"DESCRIPTION" char(100)
,"ADDRESS" varchar(22)
)
INSERT INTO #temp
SELECT
[PLC] "PLC RIO"
,[SIGNAL] SIGNAL
,[RACK] RACK
,[SLOT] SLOT
,[POINT] POINT
,[CARD] "CARD"
,[TAG] TAG
,[PROJ] PROJ
,[DESCRIPTION] "DESCRIPTION"
,[ADDRESS] "ADDRESS"
FROM [FAB28].[dbo].[PLC_TAGS2]
WHERE [PLC] <> ''
and [PLC] LIKE '%' + @filter1 + '%'
and [PLC] LIKE '%' + @filter2 + '%'
and [SLOT] LIKE '%' + @slot + '%'
ORDER BY [PLC],[RACK],[SLOT],[POINT]
END
I want to return the temporary table #temp and return #temp doesn't work. Also I want that the temp table will be dropped at the end of the function.
How can I do it?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
对于问题的第二部分 - 当存储过程完成时，在存储过程中创建的任何临时表都会自动删除 - 您无需采取任何其他操作。
To the secondary part of your question - any temp tables created within a stored procedure are automatically dropped when the stored procedure finishes - you don't have to take any additional actions.
添加：
您可以在程序末尾
。在您的 SP 中，临时表看起来是多余的。您只需将
insert语句替换为select即可立即返回数据。You would add:
at the end of the procedure.
In your SP, the temporary table looks redundant. You could just replace the
insertstatement with aselectto return the data immediately.您不会从存储过程中返回这样的临时表。您必须首先创建临时表，调用过程，然后读取临时表：所有这些都在同一连接上，因此它保持在范围内
但是，为什么不只在存储过程中进行 SELECT 并正常使用结果集呢？这里不需要临时表
You don't return temp tables like this from stored procedures. You'd have to create the temp table first, call the proc, read the temp table afterwards: all on the same connection so it stays in scope
However, why not just SELECT in the stored proc and consume the result set normally? You don't need a temp table here