Solved

Local variable scope in sql server

Posted on 2010-09-17
5
414 Views
Last Modified: 2012-05-10
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
Comment
Question by:danielivanov2
5 Comments
 
LVL 11

Expert Comment

by:aelliso3
ID: 33704054
I don't see where you are assigning a value to @v3
0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 33704087
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
 

Author Comment

by:danielivanov2
ID: 33704093
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
 
LVL 12

Expert Comment

by:jagssidurala
ID: 33704133
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
 

Accepted Solution

by:
danielivanov2 earned 0 total points
ID: 33704858
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question