Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Local variable scope in sql server

Posted on 2010-09-17
5
Medium Priority
?
418 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

722 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