473,320 Members | 2,147 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Using a "dynamic top" statement with a cursor

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
4 7657
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
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
"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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Kevin Frey | last post by:
Hello, I have a test database with table A containing 10,000 rows and a table B containing 100,000 rows. Rows in B are "children" of rows in A - each row in A has 10 related rows in B (ie. B has...
6
by: Matt | last post by:
When I do the following, the mycode will still execute. The reason I put window.opener=top; is to disable the close confirmation dialog box. window.opener = top; window.close(); //mycode... ...
0
by: raca | last post by:
I am trying to create a generic SOA ServiceInvoker that will accept an XML string that will be used to deserialize an object generated by XSDObjectGen. The hierarchy goes like this:...
1
by: Bill Smith | last post by:
If I have the line following in a stored proc... set rowCount = (select count(*) from t2); what is the equivalent way to set rowCount variable using dynamic sql... ... set sqlStmt = 'select...
7
by: kaul | last post by:
i want to create a 2-d array containg r rows and c columns by dynamic memory allocation in a single statement so that i will be able to access the ith and jth index as say arr how is that...
5
by: peppi911 | last post by:
Hi, is it possible to create a cursor from a dynamic string? Like: DECLARE @cursor nvarchar(1000) SET @cursor = N'SELECT product.product_id FROM product WHERE fund_amt > 0' ...
2
by: Łukasz W. | last post by:
Hello everybody! I have a small table "ABC" like this: id_position | value --------------------------- 1 | 11 2 | 22 3 | 33
0
by: sarakumarsg | last post by:
Hai, i would like to use dynamic sql for update records in the DB@ db. Means, the value to be updated will be send as concatenated string. Then based on the concatnation value i need to...
3
by: aj | last post by:
DB2 LUW 8.1 fixpak 14 Red Hat EL AS 4.4 I'm trying to diagnose some nocturnal CPU pressure, and am trying to understand the dynamic statement cache as it applies to LUW. The only doc/redbooks...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.