Как сделать ForEach для пользовательского типа таблицы в хранимой процедуре SQL Server?

Курсоры — это SQL-эквивалент ForEach,

Но курсоры часто являются признаком плохого SQL: они нарушают обычное мышление, основанное на наборах, на котором построен и оптимизирован SQL.

Найдите в SQL cursor или SQL Cursor Azure множество примеров, руководства и примечания по оптимизации.

Но этого недостаточно: избегайте курсоров: они часто являются костылем для программистов с других языков в SQL, и они часто медленны и сложны в обслуживании.

Почему бы не использовать курсор???

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

Ваша первая точка принятия решения всегда должна заключаться в том, чтобы увидеть, можете ли вы выполнить операцию на основе набора, а не итерацию (построчную обработку). Базы данных оптимизированы для первого. Ответ, который я даю здесь, предназначен для тех, кто решил, что они никак не могут использовать подход, основанный на наборах, и целью итерации является табличная переменная.

Ваша табличная переменная похожа на коллекцию на языке программирования. Это частная структура в памяти. Нет абсолютно никаких проблем с повторением этого в стиле ForEach, когда вы находитесь внутри хранимой процедуры. Если ваш сценарий действительно требует построчной обработки, то курсор, безусловно, подходит в вашем случае. Я действительно не понимаю, почему бы и нет.

Давайте рассмотрим пример, основанный на вашем сценарии. Сначала мы определяем тип таблицы:

CREATE TYPE [IntListType] AS TABLE
   (   [T] INT  );
GO

Затем мы определяем хранимую процедуру, которая использует эту таблицу в качестве входных данных:

CREATE PROCEDURE [myTest]
 (
       @IntListInput IntListType READONLY
 )
 AS
 BEGIN
    SET NOCOUNT ON;

    DECLARE @myInt INT;
    DECLARE intListCursor CURSOR LOCAL FAST_FORWARD
    FOR
    SELECT [T]
    FROM @IntListInput;

    OPEN intListCursor;

    -- Initial fetch attempt
    FETCH NEXT FROM intListCursor INTO @myInt;

    WHILE @@FETCH_STATUS = 0
    BEGIN
       -- Here we do some kind of action that requires us to 
       -- process the table variable row-by-row. This example simply
       -- uses a PRINT statement as that action (not a very good
       -- example).
       PRINT 'Int var is : ' + CONVERT(VARCHAR(max),@myInt);

       -- Attempt to fetch next row from cursor
       FETCH NEXT FROM intListCursor INTO @myInt;
    END;

    CLOSE intListCursor;
    DEALLOCATE intListCursor;
 END;
 GO

Итак, да, я использую курсор для итерации.

Обратите внимание, что я использую ключевые слова LOCAL и FAST_FORWARD просто для того, чтобы оптимизатору было очень ясно (явно), что я не собираюсь обновлять свой курсор, и я буду прокручивать только вперед, и я буду получать к нему доступ только внутри процедуры.

Я тестировал это так:

DECLARE @IntList IntListType;

-- Put some random data into our list
INSERT INTO @IntList VALUES (33);
INSERT INTO @IntList VALUES (777);
INSERT INTO @IntList VALUES (845);
INSERT INTO @IntList VALUES (71);


EXEC myTest @IntList;
GO

Вы можете сделать что-то похожее на это:

CREATE PROCEDURE [dbo].[testSet]
AS

BEGIN
    SET NOCOUNT ON;

    DECLARE @NumberofIntType            int,
            @RowCount                   int

    -- get the number of items
    SET @NumberofIntType = (SELECT  count(*)
                            FROM dbo.IntType)

    SET @RowCount = 0           -- set the first row to 0

    -- loop through the records 
    -- loop until the rowcount = number of records in your table
    WHILE @RowCount <= @NumberofIntType
        BEGIN
            -- do your process here

            SET @RowCount = @RowCount + 1
        END
END

небольшое исправление в приведенном выше ответе, когда я запустил proc... опечатку для @NumberofIntType

CREATE PROCEDURE [dbo].[testSet]
AS

BEGIN
    SET NOCOUNT ON;

    DECLARE @NumberofIntType            int,
            @RowCount                   int

    -- get the number of items
    SET @NumberofIntType = (SELECT  count(*)
                            FROM dbo.IntType)

    SET @RowCount = 0           -- set the first row to 0

    -- loop through the records 
    -- loop until the rowcount = number of records in your table
    WHILE @RowCount <= @NumberofIntType
        BEGIN
            -- do your process here

            SET @RowCount = @RowCount + 1
        END
END