danielivanov2
asked on
Local variable scope in sql server
I have a query with embedded cursors and i need to use one variable, declared in first cursor, to update it in second one, but it seems it is not in scope anymore, its value becomes null, despite they are in the same batch, to my knowledge. How to manage it so I can get the variable value? In code I put an schema of my block, the variable I am talking about is @v3. Thanks
DECLARE
@v1, @v2;
--
DECLARE c1 CURSOR FAST_FORWARD
FOR
SELECT …FROM … WHERE ;
OPEN cursor_subscribers;
FETCH NEXT FROM c1 INTO ….;
WHILE @@FETCH_STATUS=0
BEGIN
DECLARE @v3
DECLARE c2 CURSOR FAST_FORWARD
FOR
SELECT …FROM … WHERE ;
OPEN c2;
FETCH NEXT FROM c2
INTO ……
WHILE @@FETCH_STATUS=0
BEGIN
--do something to compute v3 variable
FETCH NEXT FROM c2 INTO ….
END
CLOSE c2;
DEALLOCATE c2;
DECLARE c3 CURSOR FAST_FORWARD
FOR
SELECT …FROM … WHERE ;
OPEN c3;
FETCH NEXT FROM c3
INTO ….
WHILE @@FETCH_STATUS=0
BEGIN
IF some_condition
BEGIN
--here I need to use v3
END
FETCH NEXT FROM c3
INTO …..
END
CLOSE c3;
DEALLOCATE c3;
FETCH NEXT FROM c1 INTO …;
END
CLOSE c1;
DEALLOCATE c1;
I don't see where you are assigning a value to @v3
since you did not show how did you manipulate (calculate) data with @v3
then we can't tell if something wrong or not.
anyway, my suggestion is, you should either PRINT or SELECT @v3 value to show inside the loop of c2 CURSOR
to confirm the value is there before continue to other process.
then we can't tell if something wrong or not.
anyway, my suggestion is, you should either PRINT or SELECT @v3 value to show inside the loop of c2 CURSOR
to confirm the value is there before continue to other process.
ASKER
its line 22: --do something to compute v3 variable
it is computed there, but in line 38 it becomes null (its a varchar type)
it is computed there, but in line 38 it becomes null (its a varchar type)
In code you did not assign any value to the @v3 variable.
if we not assign any value to the variable, by default the value of the variable is Null.
In sql server the scope of the variable starts from declaration of the variable to the end of the query(may
contains n number of loops, statements, lines etc..).
if we not assign any value to the variable, by default the value of the variable is Null.
In sql server the scope of the variable starts from declaration of the variable to the end of the query(may
contains n number of loops, statements, lines etc..).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.