Correction:
select
o.*
from
STOCKDISPOSABLE o
where
o.STOCKDEFID = @numDefId
where
@numQuantity >
(
select
count (*)
from
STOCKDISPOSABLE i
where
i.STOCKDEFID = @numDefId
and i.STOCKROWID > o.STOCKROWID
)
--
Tom
---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Tom Moreau" <to*@dont.spam.me.cips.ca> wrote in message news:OB**************@tk2msftngp13.phx.gbl...
Try:
select
o.*
from
STOCKDISPOSABLE o
where
o.STOCKDEFID = @numDefId
where
@numQuantity <
(
select
count (*)
from
STOCKDISPOSABLE i
where
i.STOCKDEFID = @numDefId
and i.STOCKROWID > o.STOCKROWID
)
BTW, why do you need a cursor?
--
Tom
---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Flapper" <flappersbits@s_o_d_o_f_f_s_p_a_m.hot_nospam_mail. com> wrote in message news:bh**********@titan.btinternet.com...
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