Cursor Scenario - 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

General SQL Server Forums

New to SQL Server Programming

Cursor Scenario

Author

Topic

ben_53
Yak Posting Veteran

67 Posts

Posted - 2011-08-29 : 15:19:18

Hi guys.
I have my_table which has 2 columns sequence, spnum
sequence has values (1,2,.... )
spnum can have only values (1,2,3,4,5,6).
spnum represents the stored procedures (6 SPs)
Logic:
now I want to run the table according to the sequence(increasing order)
and check the spNum, if it is 1 then execute Sp1
if SPnum = 2 then execute sp2 and so on...

then after finding the appropriate SP, it should move to sequence 2
and so on...

My Code:

declare @spnum varchar(20)

declare cur CURSOR LOCAL for
select spnum from my_table

open cur

fetch next from cur into @spnum

while @@FETCH_STATUS = 0

BEGIN

exec sp1 when spnum =1 /**** Bad Logic ****/

fetch next from cur into @spnum
END

close cur
deallocate cur

please help !

ben_53
Yak Posting Veteran

67 Posts

Posted - 2011-08-29 : 15:25:25

BEGIN
select spnum = case
when '1' then exec sp1 ....

error: An expression of non-boolean type specified in a context where a condition is expected, near 'then'.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-08-29 : 17:03:04

You should have continued with this thread:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=164714

I agree with Visakh16, I'm SURE there's a better way to do whatever you're doing. But to answer your question - here is the syntax you're looking for:


if @spnum = 1 
       exec sp1
else if @spnum = 2
       exec sp2
else if @spnum = 3
       exec sp3
etc...

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources