Solved

Local variable scope in sql server

Posted on 2010-09-17
5
411 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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
sql query 7 34
c# code 19 58
How to find all values in a column which is not money 25 24
Link SQL table to Webpage 9 37
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

707 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now