Объявить переменную произвольного типа в цикле курсора

Question

Во время расследования ошибки в нашем проекте я столкнулся с каким-то странным для меня поведением. Проверьте код:

--CREATE TYPE dbo.test AS TABLE(DocumentVersionId INT, ResourceId INT, Deadline DATE)

DECLARE @documents dbo.test
DECLARE @resourceId INT

INSERT INTO @documents(DocumentVersionId, ResourceId, Deadline)
SELECT * FROM (VALUES(1, 1, GETDATE()),(2, 2, GETDATE()),(3, 3, GETDATE())) T(A, B, C)

DECLARE mails CURSOR FAST_FORWARD FOR
SELECT DISTINCT ResourceId 
FROM @documents

OPEN mails

FETCH NEXT FROM mails INTO @resourceId

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @docs dbo.test

    --DELETE FROM @docs

    INSERT INTO @docs(DocumentVersionId, Deadline)
    SELECT DocumentVersionId, Deadline
    FROM @documents
    WHERE ResourceId = @resourceId

    SELECT * FROM @docs

    FETCH NEXT FROM mails INTO @resourceId
END

CLOSE mails
DEALLOCATE mails

У нас есть процедура, которая генерирует письма для данных, передаваемых TVP. Каждое выполнение этой процедуры должно выполняться в цикле (курсоре) для отфильтрованных данных. Проблема заключается в том, что на каждой итерации цикла переменная @docs содержит данные предыдущих итераций, даже если переменная воссоздается каждый раз.

Мы можем удалять данные из этой переменной каждый раз, но это не то поведение, которое мы ожидаем. Вопрос в том, почему это происходит?

Answer 1

Переменные не имеют локальной области видимости. Из документов:

Область действия переменной - это диапазон операторов Transact-SQL, которые могут ссылаться на переменную. Область действия переменной длится с момента ее объявления до конца пакета или хранимой процедуры, в которой она объявлена.

Который означает, что @docs находится в области действия от первой итерации цикла, пока процедура не завершится. Это более или менее то, как работает область в VB6, и это создает... интересные проблемы с областями видимости.

Вам придется позвонить DELETE FROM @docs; в каждой итерации, чтобы очистить его