Help please,

Have a situation when converting from Oracle SP's to SQL SP's. The old
oracle cursor was roughly as follows

CURSOR cur_rsStock IS
select
*
from
(select StockRowId, CategoryId
from
STOCKDISPOSABLE
where
STOCKDEFID=numDefId
ORDER BY
STOCKROWID
)
where
ROWNUM <= numQuantity;

The closest I can get in MS SQL is as follows :
declare cur_rsStock
CURSOR for
select top @numQuantity
StockRowId, CategoryId
from
STOCKDISPOSABLE
where
STOCKDEFID=numDefId
ORDER BY
STOCKROWID

But, SQL doesn't allow variables next to top. I know I can assign the whole
select statement to a string and use exec to exec the string to get a
recordset but how can I point a cursor to receive its output?

i.e.
set @strSQl = select top ' + @numQuantity + ' StockRowId, CategoryId
.......
exec @strSQL

but how do I do

declare cur_rsStock
set cur_rsStock = ( exec @strSQL)



Flapper

Try this:

DECLARE cur_rsStock CURSOR FOR
SELECT StockRowId, CategoryId
FROM stockdisposable
WHERE stockdefid = numdefid AND
(SELECT COUNT(*)
FROM stockdisposable AS S
WHERE stockdefid = numdefid
AND stockrowid <= stockdisposable.stockrowid)
<= @numquantity

But possibly this is not the best approach. Look at the entire SP and see if
you can replace the cursor with set-based statements. Solve the business
requirement rather than feel constrained by the way the Oracle guys did it.

--
David Portas
------------
Please reply only to the newsgroup
--