CODEWITHSUNDEEP
sql-server
mHelpMe
mHelpMe

Reputation: 6668

adding a second if else statement to a stored procedure

I have a stored procedure like below which works fine.

declare db_cursor cursor for
select Atmosphere, Region, PreATR
from myTbl

open db_cursor
fetch next from db_cursor into @Atmosphere, @Region, @PreATR


while @@FETCH_STATUS = 0
begin

   if @PreATR = 1
      set @q = 'insert into tblA ... '       
   else
      set @q = 'insert into tblB ...
   end

   exec(@q)

   fetch next from db_cursor into @Atmosphere, @Region, @PreATR
end

close db_cursor
deallocate db_cursor

However now I need to adjust it. So I want to add another if else statement like below. When I do this though the line below becomes highlighted

close db_cursor

Incorrect syntax near 'close'. expecting CONVERSATION

    open db_cursor
fetch next from db_cursor into @Atmosphere, @Region, @PreATR


while @@FETCH_STATUS = 0
begin

   if @Region = 55
      set @someVar = 1
   else
      set @someVar = 1
   end

   if @PreATR = 1
      set @q = 'insert into tblA ... '       
   else
      set @q = 'insert into tblB ...
   end

   exec(@q)

   fetch next from db_cursor into @Atmosphere, @Region, @PreATR
end

close db_cursor
deallocate db_cursor

why does adding this extra if else statement cause this behavior?

Upvotes: 0

Views: 58

Answers (2)

GuidoG
GuidoG

Reputation: 12079

Im sure you could do this much simpler and faster without a cursor, unfortunate there is not enough details in the question to write an example that can get you started

probably you need something like this

insert into tblA (your fields here)
select t.Atmosphere, t.Region, t.PreATR
from   myTbl t
where  t.PreATR = 1
and    more conditions here...

insert into tblB (your fields here)
select t.Atmosphere, t.Region, t.PreATR
from   myTbl t
where  t.PreATR <> 1
and    more conditions here...

Upvotes: 1

Mukesh Arora
Mukesh Arora

Reputation: 1813

You are getting the error because if else syntax was incorrect and also single quote was missing in the second insert statement, try below-updated query, I have removed end after else statement and added a single quote in the second insert statement -

open db_cursor
fetch next from db_cursor into @Atmosphere, @Region, @PreATR


while @@FETCH_STATUS = 0
begin

   if @Region = 55
      set @someVar = 1
   else
      set @someVar = 1


   if @PreATR = 1
      set @q = 'insert into tblA ... '       
   else
      set @q = 'insert into tblB ...'


   exec(@q)

   fetch next from db_cursor into @Atmosphere, @Region, @PreATR
end

close db_cursor
deallocate db_cursor

Note: Whenever there are multiple statements with if and else block then you must use begin and end like below - 

IF @Var = 1
BEGIN
    PRINT '1';
END
ELSE
BEGIN
    PRINT 'not 1';
END

Upvotes: 1

Related Questions