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

问题描述：

我有一个存储过程，它接受一个输入参数 @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 等效项，难以维护且容易出错.

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

相关推荐