Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Local variable scope in sql server

Posted on 2010-09-17
5
Medium Priority
?
419 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

971 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