Assigning variables in a cursor

[fcoomermd]

i need to assign a value to variable from the sql statement within a cursor. how can this be done. needs to be because all the logic is within the SP (done for testing purposes, will have a business layer for the application). the following is my code:

declare curEmp cursor for

select * from tblFirm f, tblEmployee e, tblEmpClassCode ecc
where
f.firmID=e.firmID and
f.firmID='1' and
e.employeeID='1' and
ecc.firmID=f.firmID and
ecc.employeeID=e.employeeID
open curEmp

fetch next from curEmp

while (@@fetch_status=0)
begin
-- HERE IS WHERE ADDITIONAL STATMENTS WOULD BE USING
-- THE VARIABLES CREATED FROM THE 1ST STATEMENT...
-- HOW CAN I ASSIGN VALUES?
fetch next from curEmp
end
close curEmp
deallocate curEmp


thanks for any help

 

[aalmeida]

try something like this:

select Col1, Col2 from tblFirm f, tblEmployee e, tblEmpClassCode ecc
where
f.firmID=e.firmID and
f.firmID='1' and
e.employeeID='1' and
ecc.firmID=f.firmID and
ecc.employeeID=e.employeeID
open curEmp

fetch next from curEmp into @MyVar1, @MyVar2 ( as many variables as columns the Select statment returns)


AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[nigelrivett]

Your fetch statement can have a variable list to receive the values in the row if that's what you mean.

Think about whather you need a cursor though - they are never necessary.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[SQLSister]

Agree with nigel, cursors should only be used as a last resort and almost never from a user interface. YOu don;t say what you indended to do within the cursor, so it's kind of hard to show you a set-based solution. But cursors are extremely inefficient and should not be used if there is a set-based solution.

Questions about posting. See faq183-874

 

[fcoomermd]

using cursors only to simulate the business rules in code, and with SP's... only for testing... thanks