如何将临时表作为参数传递给单独的存储过程 [英] How to pass a temp table as a parameter into a separate stored procedure

问题描述

我有一个存储过程，它接受一个输入参数 @CategoryKeys varchar，并将其内容解析到一个临时表中，#CategoryKeys.

I have a stored procedure that takes an input parameter @CategoryKeys varchar, and parses its contents into a temp table, #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?

推荐答案

创建#TEMP 表时，范围"比创建它的过程要大.

When you create a #TEMP table, the "scope" is bigger than just the procedure it is created in.

以下是示例:

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

话虽如此，请不要对这些进行大量编码.它是全局变量的 SQL 等效项，难以维护且容易出错.

这篇关于如何将临时表作为参数传递给单独的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！