By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,320 Members | 2,109 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,320 IT Pros & Developers. It's quick & easy.

Using a "dynamic top" statement with a cursor

P: n/a
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


Jul 20 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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
--

Jul 20 '05 #2

P: n/a
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

Jul 20 '05 #3

P: n/a
"Tom Moreau" <to*@dont.spam.me.cips.ca> wrote in message
news:OB**************@tk2msftngp13.phx.gbl...
Try:
Thanks for the tip will try it out.
BTW, why do you need a cursor?


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



Jul 20 '05 #4

P: n/a
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
--
Jul 20 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.