SQLTeam.com | Weblogs | Forums

Understanding

Transact-SQL
#1

HI Team need help in understanding the below code. I'm new to the programming.

I've 100 records in table abc.

declare @counter int
set @counter=1

while @counter>0
begin
begin
select * into #a from abc;
end;
begin
update x set y=m
from a x inner join b y
on x.id=i.id;
end;
begin
drop table #a
end;

select @counter=@@rowcount
end
My understanding it the loop will never encounter 0. so it become an infinite loop.

please help me in understand what the "select @counter=@@rowcount" will do and the loop will exit.

Sorry james there is small correction in code.

#2

To the contrary, since you are starting out with @counter=1, it will never enter the while loop which requires that @counter be greater than 1.

If you changed the initial condition to something else, or changed the while condition, it would enter the loop, but it will fail the second time when you pass through the loop. This is because the SELECT INTO construct creates a table and inserts the data into it. Since the table #a would have been created at the first pass, at the second pass, the code will error out.

It would help you to understand the logic if you use proper indentation. Perhaps you are, and the forum formatting isn't doing its job.

#3

Sorry James there is a small correction. typo error.

#4

Some SQL statements set @@ROWCOUNT, some don't. DROP TABLE does seem to set it to 0, so it looks like the WHILE will end. Although for that code, of course there's no need for a loop at all.

select object_id into #a from sys.tables
select @@rowcount
drop table #a
select @@rowcount
#5

Thank you Scott for the reply. I'm new to the SQL SERVER. I replicated my prod code trying to break my head why the loop is written when there is no need for it. I'm just thinking is my understanding is correct or not. When I touch based with end user he said like in initial iteration the update will work in next iteration the loop will exit as there are no update is need in it. I missed to add one logic in update. Will update in it now.

#6

why are you doing things in a loop? what is the requirement to do this in a loop?

#7

we are converting this code to the SAS trying to understand what this code is doing. I'm not aware the SQLSERVER. trying to get any idea what the coding is doing. I can covert to sas program