Reputation: 6668
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
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
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