For to next or do while loop in SP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

For to next or do while loop in SP

Author

Topic

shifis
Posting Yak Master

157 Posts

Posted - 2006-04-11 : 11:58:32

Exist a funtion that I can use in a SP that do something like the for to next or Do while Loop do?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-04-11 : 12:00:57

Look up WHILE in Books Online..

X002548
Not Just a Number

15586 Posts

Posted - 2006-04-11 : 12:32:29

how about set based logic

what are you trying to accomplish?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

shifis
Posting Yak Master

157 Posts

Posted - 2006-04-11 : 17:38:03

Im trying to do this, but i cant make it work

declare @intNoTarjIni as float
declare @intNoTarjFin as float
declare @err as int

set @intNoTarjIni = 123456789
set @intNoTarjFin = 123456799

while @intNoTarjIni < @intNoTarjFin
begin
BEGIN TRAN
insert tbTarjetas
values(@intNoTarjIni, 'I')

SELECT @err = @@error
IF (@err <> 0)
BEGIN
ROLLBACK TRAN
GOTO ERR_HANDLER
BREAK
END
else
COMMIT TRAN
end
begin
ERR_HANDLER:
select 0 as result
end

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-04-12 : 05:23:37

That will give you an infinite loop because @intNoTarjIni is always less than @intNoTarjFin. Try changing one of them somewhere in the loop - e.g. increment @intNoTarjIni...

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-04-12 : 05:32:01

By the way, since Brett mentioned set-based logic, you might want to be aware of this kind of technique. Just run it to see...

--data
declare @tbTarjetas table (x float, c char(1))

declare @intNoTarjIni as float
declare @intNoTarjFin as float

set @intNoTarjIni = 123456789
set @intNoTarjFin = 123456799

--calculation
declare @numbers table (i int identity(0, 1), x bit)
insert @numbers select top 1000 null from master.dbo.syscolumns a, master.dbo.syscolumns b

insert @tbTarjetas select @intNoTarjIni + i, 'I' from @numbers where i < @intNoTarjFin - @intNoTarjIni
select * from @tbTarjetas

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources