Using a "dynamic top" statement with a cursor

[Flapper]

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=numD efId
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=numD efId
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

[David Portas]

Re: Using a &quot;dynami c top&quot; statement with a cursor

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
--

[Tom Moreau]

Re: Using a &quot;dynami c top&quot; statement with a cursor

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

https://www.pinnaclepublishing.com/sql

"Tom Moreau" <tom@dont.spam. me.cips.ca> wrote in message news:OBVyWGbYDH A.3248@tk2msftn gp13.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

https://www.pinnaclepublishing.com/sql

"Flapper" <flappersbits@s _o_d_o_f_f_s_p_ a_m.hot_nospam_ mail.com> wrote in message news:bhdlj9$4h9 $1@titan.btinte rnet.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=numD efId
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=numD efId
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

[Flapper]

Re: Using a &quot;dynami c top&quot; statement with a cursor

"Tom Moreau" <tom@dont.spam. me.cips.ca> wrote in message
news:OBVyWGbYDH A.3248@tk2msftn gp13.phx.gbl...
[color=blue]
>Try:[/color]

Thanks for the tip will try it out.
[color=blue]
> BTW, why do you need a cursor?[/color]

If I could find a way without I'd do it. Having worked with nested
recordsets and cursors for many years, it takes a bit of thinking to
translate to set based working. Maybe you can help me!!!

Situation is this :

have 'master' table with lots of stock items
have 'slave' table which contains some items as found in the 'master'

when 'slave' table gets low on stock in places a request for more from the
'master'

we then need to :

a) copy the first 'qty' of records from the 'master' to the 'slave' (have to
ensure that products sold in order they arrived & 'qty' is variable)
b) update the 'master' as being moved (so not grabbed next time)

currently the cursor selects the top/first 'qty' records from master
for each record in the cursor, we then perform an insert into 'slave' and an
update of 'master'


in set based terms, I guess we would want :

insert into slave (select top 'qty' from master)

update top 'qty' master set sold to slave


im just trying to determine if its quicker to find a solution to the cursor
top problem and retain exisiting functionality or redo with sets. currently,
i can't get either to work!


Flapper

[David Portas]

Re: Using a &quot;dynami c top&quot; statement with a cursor

This seems like a strange design. Why two tables of stock? What are the
enttities represented by Master and Slave? Can't you just have one table
with a column to indicate whether stock items belong to the "master" or
"slave"?

If you need more help with the set-based solution please post CREATE TABLE
statements for the tables and post some sample data as INSERT statements.

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