如何将临时表作为参数传递到单独的存储过程中

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?

如果临时表有行，我希望将该表传递到一个单独的存储过程中。如何在单独的过程中创建一个参数来保存临时表?

2 个解决方案

#1

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

    /* 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，很难维护和容易出错。

#2

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
将XML传递到存储过程中
Pass a table-valued parameter into the stored procedure
将表值参数传递给存储过程

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 READONLY is required since only IN is supported and the table cannot be changed)

然后，您可以将该类型用作存储的proc的参数(只读是必需的，因为只支持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的表变量直接调用存储的proc。

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，则可以将SQL参数从匹配用户定义类型的DataTable类型传递。

Sample output from the query:

查询输出示例:

Key
-----------
1
2

#1