August 17, 2011 at 7:07 am
Create Table #Table (id int,Empname varchar(10),Job varchar(10))
Insert into #Table values (1,'sharath',null)
Insert into #Table values (2,'TURNER','SALESMAN')
Insert into #Table values (3,'ADAMS',null)
Insert into #Table values (1,'chandu',null)
Create Table emp (Empno int,name varchar(10),job varchar(10))
Insert into emp values (7844,'TURNER','SALESMAN')
Insert into emp values (7876,'ADAMS','')
declare @var1 int
Declare @var2 varchar(10)
Declare @var3 varchar(10)
declare cur1 cursor for
Select id,column1,column2 From #Table
open cur1
fetch cur1 into @var1,@var2,@var3
WHILE @@Fetch_Status = 0
begin
declare @secVar3 varchar(10)
--set @secVar3 = null
select @secVar3 = EMPNO From EMP where name = @var2
Print @secVar3
Fetch next From cur1 into @var1,@var2,@var3
END
CLOSE cur1
DEALLOCATE cur1
if you look at the print statement when you run the cursor it is not behaving properly.
i am decalring the variable @secVar3 inside the loop of the cursor. so for each time it need to set to the null value. bu the Print statements shows that the value for the last Row (Chandu) does not have any match in the EMp table but still it show 7876 in the print statement.
when i un comment the line
--set @secVar3 = null
it is behaving correctly.
My Question here is, why i need to set the value to null if i am decalring it. is this one is by design.
August 17, 2011 at 7:27 am
If your read: http://msdn.microsoft.com/en-us/library/ms188927.aspx
You will find that the scope of a local variable is the batch in which it is declared (you are not in c# or vb :-)).
That's is why you need to set it to null, declare itlsef will not work as you expected (even in SQL2008 where you could use syntax like "declare @secVar3 varchar(10) = null").
August 17, 2011 at 7:47 am
but still the statement in the link you provided says that
"After declaration, all variables are initialized as NULL, unless a value is provided as part of the declaration."
so when i am declaring it should be initialised as null not to the value of the Previous fetch statement of the cursor.
if you can look at he example below it will through an error called
'Variable names must be unique within a query batch or stored procedure.'
Declare @var1 varchar(10)
Set @var1 = 'shar'
print @var1
Declare @var1 varchar(10)
print @var1
August 17, 2011 at 8:06 am
To be honest, I'm surprised you're not getting that message about variable names being unique, since you're declaring it again and again in the same batch. But if it worries you, just leave that SET statement uncommented.
The important question is, however, why are you using a cursor to do this instead of just joining the two tables? As soon as the tables get to more than a few thousand rows, you're going to see performance suffer badly.
John
August 17, 2011 at 8:11 am
Maybe the problem is that you are using a cursor in the first place. Cursors are horribly inefficient and are rarely the optimal solution to the problem.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 17, 2011 at 8:16 am
sharath.chalamgari (8/17/2011)
but still the statement in the link you provided says that"After declaration, all variables are initialized as NULL, unless a value is provided as part of the declaration."
so when i am declaring it should be initialised as null not to the value of the Previous fetch statement of the cursor.
if you can look at he example below it will through an error called
'Variable names must be unique within a query batch or stored procedure.'
Declare @var1 varchar(10)
Set @var1 = 'shar'
print @var1
Declare @var1 varchar(10)
print @var1
John, this is an example that i created for the problem i faced in the realtime scenario of the cursor where i need to pick the value and insert/update multiple tables.
In my case it is bit diffcult to replace the entire cursor.
August 17, 2011 at 8:17 am
sharath.chalamgari (8/17/2011)
but still the statement in the link you provided says that"After declaration, all variables are initialized as NULL, unless a value is provided as part of the declaration."
so when i am declaring it should be initialised as null not to the value of the Previous fetch statement of the cursor.
if you can look at he example below it will through an error called
'Variable names must be unique within a query batch or stored procedure.'
Declare @var1 varchar(10)
Set @var1 = 'shar'
print @var1
Declare @var1 varchar(10)
print @var1
John, this is an example that i created for the problem i faced in the realtime scenario of the cursor where i need to pick the value and insert/update multiple tables.
In my case it is bit diffcult to replace the entire cursor.
August 17, 2011 at 8:20 am
In my case it is bit diffcult to replace the entire cursor.
Difficult if you are used to programming in a procedural language, maybe, but this is the sort of stuff T-SQL is designed for. I'd be very surprised if you need that cursor.
John
August 17, 2011 at 8:27 am
sharath.chalamgari (8/17/2011)
but still the statement in the link you provided says that"After declaration, all variables are initialized as NULL, unless a value is provided as part of the declaration."
so when i am declaring it should be initialised as null not to the value of the Previous fetch statement of the cursor.
if you can look at he example below it will through an error called
'Variable names must be unique within a query batch or stored procedure.'
Declare @var1 varchar(10)
Set @var1 = 'shar'
print @var1
Declare @var1 varchar(10)
print @var1
It's just your expectations based on standard procedural way of coding.
But.
You should get used to it: T-SQL is not procedural language...
In your latest example, query compiler can see that you've declared the same variable twice, so you've got the error!
If you declare variable in the loop, compiler is happy. Then, when the query goes through query optimiser it moves declaration of the variable to the top, so it will be declared and all required reservations made. And, No you cannot use variable before you declare it, compiler will check it first 😉
You will just need to take it as it is as I don't think this behaviour is going to be changed in any near future just because is not meeting your expectations.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply