Как передать временную таблицу в качестве параметра в отдельную хранимую процедуру

У меня есть хранимая процедура, которая принимает входной параметр @CategoryKeys varchar и анализирует его содержимое в таблице temp, #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

Если в таблице temp есть строки, я хотел бы передать таблицу в отдельную хранимую процедуру. Как я могу создать параметр в отдельной процедуре, чтобы удерживать временную таблицу?

Ответ 1

Когда вы создаете таблицу #TEMP, "область" больше, чем просто процедура, в которой она создана.

Ниже приведен пример:

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

СКАЗАЛ, ЧТО, ПОЖАЛУЙСТА, НЕ КОДИРУЙТЕ ТОЛЬКО ЭТИХ. ЕЕ SQL-ЭКВИВАЛЕНТ ГЛОБАЛЬНОЙ ПЕРЕМЕННОЙ И ЭТО ТРУДНО СОХРАНИТЬ И ПРОБЛАТЬ ПРОН.

Ответ 2

Хотя понимание областей охвата является прямой потребностью, подумал, что было бы полезно добавить еще несколько вариантов в смесь, чтобы подробно изложить предложения из комментариев.

Передача XML в хранимую процедуру
Передайте параметр хранимой таблицы в хранимую процедуру

1. Передайте XML в хранимую процедуру.

Когда XML передается в параметр, вы можете использовать XML непосредственно в своих SQL-запросах и присоединяться/применять к другим таблицам:

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

Затем вызов хранимой процедуры для тестирования:

DECLARE @Text XML = '<keys><key>1</key><key>2</key></keys>'
EXEC sp_PassXml @Text

Пример вывода простого запроса.

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

2. Передайте табличный параметр в хранимую процедуру

Во-первых, вы должны определить определяемый пользователем тип для переменной таблицы, которая будет использоваться хранимой процедурой.

CREATE TYPE KeyTable AS TABLE ([Key] INT)

Затем вы можете использовать этот тип в качестве параметра для сохраненного proc (READONLY требуется, поскольку поддерживается только IN и таблица не может быть изменена)

CREATE PROC sp_PassTable
    @Keys KeyTable READONLY
AS
BEGIN
    SET NOCOUNT ON
    SELECT * FROM @Keys
END
GO

Затем сохраненный proc можно вызвать с помощью переменной таблицы непосредственно из SQL.

DECLARE @Keys KeyTable
INSERT @Keys VALUES (1), (2)
EXEC sp_PassTable @Keys

Примечание. Если вы используете.NET, вы можете передать параметр SQL из типа DataTable, соответствующего типу, определенному пользователем.

Пример вывода из запроса:

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