SQL 如何将临时表作为参数传递给单独的存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20105443/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to pass a temp table as a parameter into a separate stored procedure
提问by bsivel
I have a stored procedure that takes an input parameter @CategoryKeys varchar, and parses its contents into a temp table, #CategoryKeys.
我有一个存储过程，它接受一个输入参数@CategoryKeys varchar，并将其内容解析到一个临时表中#CategoryKeys。
-- create the needed temp table.
CREATE TABLE #CategoryKeys
(
CategoryKey SMALLINT
);
-- fill the temp table if necessary
IF Len(rtrim(ltrim(@CategoryKeys))) > 0
BEGIN
INSERT INTO #CategoryKeys
(CategoryKey)
SELECT value
FROM dbo.String_To_SmallInt_Table(@CategoryKeys, ',');
END
If the temp table has rows, I would like to pass the table into a separate stored procedure. How would I go about creating a parameter in the separate procedure to hold the temp table?
如果临时表有行，我想将该表传递到一个单独的存储过程中。我将如何在单独的过程中创建一个参数来保存临时表？
采纳答案by granadaCoder
When you create a #TEMP table, the "scope" is bigger than just the procedure it is created in.
创建#TEMP 表时，“范围”比创建它的过程要大。
Below is a sample:
下面是一个示例：
IF EXISTS
(
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = N'PROCEDURE' and ROUTINE_SCHEMA = N'dbo' and ROUTINE_NAME = N'uspProc002'
)
BEGIN
DROP PROCEDURE [dbo].[uspProc002]
END
GO
CREATE Procedure dbo.uspProc002
AS
BEGIN
/* Uncomment this code if you want to be more explicit about bad "wiring" */
/*
IF OBJECT_ID('tempdb..#TableOne') IS NULL
begin
THROW 51000, 'The procedure expects a temp table named #TableOne to already exist.', 1;
end
*/
/* Note, I did not Create #TableOne in this procedure. It "pre-existed". An if check will ensure that it is there. */
IF OBJECT_ID('tempdb..#TableOne') IS NOT NULL
begin
Insert into #TableOne ( SurrogateKey , NameOf ) select 2001, 'uspProc002'
end
END
GO
IF EXISTS
(
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = N'PROCEDURE' and ROUTINE_SCHEMA = N'dbo' and ROUTINE_NAME = N'uspProc001'
)
BEGIN
DROP PROCEDURE [dbo].[uspProc001]
END
GO
CREATE Procedure dbo.uspProc001 (
@Param1 int
)
AS
BEGIN
IF OBJECT_ID('tempdb..#TableOne') IS NOT NULL
begin
drop table #TableOne
end
CREATE TABLE #TableOne
(
SurrogateKey int ,
NameOf varchar(12)
)
Insert into #TableOne ( SurrogateKey , NameOf ) select 1001, 'uspProc001'
Select * from #TableOne
EXEC dbo.uspProc002
Select * from #TableOne
IF OBJECT_ID('tempdb..#TableOne') IS NOT NULL
begin
drop table #TableOne
end
END
GO
exec dbo.uspProc001 0
HAVING SAID THAT, PLEASE DO NOT CODE UP ALOT OF THESE. ITS THE SQL EQUIVALENT OF A GLOBAL VARIABLE AND IT IS DIFFICULT TO MAINTAIN AND BUG PRONE.
话虽如此，请不要对这些进行大量编码。它是全局变量的 SQL 等价物，难以维护且容易出错。
回答by Jason W
While understanding scoping addresses the direct need, thought it might be useful to add a few more options to the mix to elaborate on the suggestions from the comments.
虽然理解范围界定解决了直接需求，但认为在组合中添加更多选项以详细说明评论中的建议可能会很有用。
- Pass XML into the stored procedure
- Pass a table-valued parameter into the stored procedure
- 将 XML 传递到存储过程
- 将表值参数传递给存储过程
1. Pass XML into the stored procedure
1.将XML传入存储过程
With XML passed into a parameter, you can use the XML directly in your SQL queries and join/apply to other tables:
通过将 XML 传递给参数，您可以直接在 SQL 查询中使用 XML 并加入/应用到其他表：
CREATE PROC sp_PassXml
@Xml XML
AS
BEGIN
SET NOCOUNT ON
SELECT T.Node.value('.', 'int') AS [Key]
FROM @Xml.nodes('/keys/key') T (Node)
END
GO
Then a call to the stored procedure for testing:
然后调用存储过程进行测试：
DECLARE @Text XML = '<keys><key>1</key><key>2</key></keys>'
EXEC sp_PassXml @Text
Sample output of a simple query.
简单查询的示例输出。
Key
-----------
1
2
2. Pass a table-valued parameter into the stored procedure
2. 向存储过程传递一个表值参数
First, you have to define the user defined type for the table variable to be used by the stored procedure.
首先，您必须为存储过程要使用的表变量定义用户定义的类型。
CREATE TYPE KeyTable AS TABLE ([Key] INT)
Then, you can use that type as a parameter for the stored proc (the READONLYis required since only INis supported and the table cannot be changed)
然后，您可以使用该类型作为存储过程的参数（这READONLY是必需的，因为仅IN支持并且表不能更改）
CREATE PROC sp_PassTable
@Keys KeyTable READONLY
AS
BEGIN
SET NOCOUNT ON
SELECT * FROM @Keys
END
GO
The stored proc can then be called with a table variable directly from SQL.
然后可以直接从 SQL 使用表变量调用存储过程。
DECLARE @Keys KeyTable
INSERT @Keys VALUES (1), (2)
EXEC sp_PassTable @Keys
Note: If you are using .NET, then you can pass the SQL parameter from a DataTable type matching the user defined type.
注意：如果您使用 .NET，那么您可以从匹配用户定义类型的 DataTable 类型传递 SQL 参数。
Sample output from the query:
查询的示例输出：
Key
-----------
1
2
回答by Hemant
Stored proc that uses temp table
使用临时表的存储过程
CREATE OR ALTER Procedure Engine.TestProcTempTable
AS
BEGIN
--DROP TABLE IF EXISTS #TestProcTempTable ;
SELECT * from #TestProcTempTable;
END
Create put data in to temp table which will be used by SP
将数据创建到临时表中，供 SP 使用
DROP TABLE IF EXISTS #TestProcTempTable ;
select * into #TestProcTempTable from <TABLE_NAME>;
execute Engine.TestProcTempTable
