do loop in sql server - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

do loop in sql server

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2005-04-06 : 02:21:32

i'm trying to convert some asp code to be done in a stored procedure

can someone help me with syntax for a do while loop

the variables would be brought into the sp as this will be done before adding the record

for example how would I do the following:

do while mypercent>.95
if cdbl(price1)>2000000 then newprice=1100000
if cdbl(price1)> 100000< 20000 then newprice=10000
loop

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-04-06 : 02:30:19

while mypercent>.95
Begin
if cdbl(price1)>2000000 newprice=1100000
if cdbl(price1)> 100000< 20000 newprice=10000
...Increare or Decrease the value of mypercent
end

Madhivanan

Failing to plan is Planning to fail

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2005-04-06 : 02:49:11

Thanks for your help.
I am getting an error --
incorrect syntax near <

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-04-06 : 02:54:12

Why do you need to use a loop, what exactly are you trying to do?
You could possibly use a CASE statement instead

Eg


CASE WHEN (mypercent > .95 AND price1 > 2000000) THEN 1100000
     WHEN (mypercent > .95 AND (price1 BETWEEN 20000 AND 100000)) THEN 10000
     WHEN (mypercent > .95 AND price1 > 100000 AND price1 < 20000) THEN 10000
     ELSE price1 
END AS newprice

Andy

Beauty is in the eyes of the beerholder

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-04-06 : 02:54:53

That sholud be

if cdbl(price1)> 100000 and cdbl(price1) < 20000 newprice=10000

Madhivanan

Failing to plan is Planning to fail

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2005-04-06 : 03:15:11

I need the loop

I am doing
if @mypercent>.95
begin
while @mypercent>.95
Begin
if @price1>2000000 select @newprice=1100000
if @price1> 100000 and @price1< 20000 select @newprice=10000
@mypercent=@newprice/@price2
end
end

I am getting an error

Incorrect syntax near the keyword 'end'.

why? Can i do a loop within the if statement

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-04-06 : 03:58:21

if @mypercent>.95
begin
while @mypercent>.95
Begin
if @price1>2000000 select @newprice=1100000
if @price1> 100000 and @price1< 20000 select @newprice=10000
set @mypercent=@newprice/@price2
end
end

Beauty is in the eyes of the beerholder

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2005-04-06 : 04:01:27

Still the same error -- any other ideas?

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-04-06 : 04:18:05

Post your full code

Beauty is in the eyes of the beerholder

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2005-04-06 : 05:24:51

Thanks for your help -- i found the error

kimberr
Starting Member

9 Posts

Posted - 2005-04-06 : 06:54:22

What was the error?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-04-06 : 07:02:23

Try this
if @mypercent>.95
while @mypercent>.95
Begin
if @price1>2000000 select @newprice=1100000
if @price1> 100000 and @price1< 20000 select @newprice=10000
@mypercent=@newprice/@price2
end

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources