declare variable of custom type in cursor loop

Question

During investigation of a bug in our project I came across some strange to me behaviour. Check the code:

--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

We have procedure that generate mails for data transfered by TVP. Each execution of this procedure must be executed in a loop(cursor) for filtered data. The problem is that, on each loop iteration, the @docs variable contains data from previous iterations even when the variable is recreating each time.

We can delete data from this variable each time, but this is not the behaviour we expect. The question is, why is that happening?

You assume that @docs has local scope. Are you sure that is indeed correct? — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Feb 23, 2016 at 13:16
It's a bit strange to me that it does not raise "variable already defined" at compile time... — IVNSTN
– IVNSTN, Commented Feb 23, 2016 at 15:01

Panagiotis Kanavos · Accepted Answer · 2016-02-23 13:21:18Z

1

Variables do not have local scope. From the docs:

The scope of a variable is the range of Transact-SQL statements that can reference the variable. The scope of a variable lasts from the point it is declared until the end of the batch or stored procedure in which it is declared.

Which means that @docs is in scope from the first loop iteration until the procedure terminates. This is more-or-less how scope worked in VB6 and it did create some ... interesting scoping issues.

You'll have to call DELETE FROM @docs; in each iteration to clear it

answered Feb 23, 2016 at 13:21

Panagiotis Kanavos

134k16 gold badges211 silver badges270 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

declare variable of custom type in cursor loop

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related