Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 420
  • Last Modified:

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;

Open in new window

0
danielivanov2
Asked:
danielivanov2
1 Solution
 
aelliso3Commented:
I don't see where you are assigning a value to @v3
0
 
JoeNuvoCommented:
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.
0
 
danielivanov2Author Commented:
its line 22: --do something to compute v3 variable
it is computed there, but in line 38 it becomes null (its a varchar type)
0
 
jagssiduralaCommented:
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..).
0
 
danielivanov2Author Commented:
the example shown is just schematic, the real scripts is far more complex

but meanwhile I have found out the problem cause:
the v3 variable was multiplied with another variable, but that variable had a null value (due an import error into a reference table), so it wasn't a scope problem
but thanks guys for your quick replies

0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now