Professional Web Applications Themes

Using a "dynamic top" statement with a cursor - Microsoft SQL / MS SQL Server

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

  1. #1

    Default 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




    Flapper Guest

  2. #2

    Default Re: Using a "dynamic top" 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
    www.pinnaclepublishing.com/sql


    "Tom Moreau" <spam.me.cips.ca> wrote in message news: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" <hot_nospam_mail.com> wrote in message news:bhdlj9$4h9$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





    Tom Guest

  3. #3

    Default Re: Using a "dynamic top" 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
    --



    David Guest

  4. #4

    Default Re: Using a "dynamic top" statement with a cursor

    "Tom Moreau" <spam.me.cips.ca> wrote in message
    news:phx.gbl...
     

    Thanks for the tip will try it out.
     

    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







    Flapper Guest

  5. #5

    Default Re: Using a "dynamic top" 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
    --


    David Guest

Similar Threads

  1. Setting "URL Behaviour" to "Dynamic" has no effect
    By Hans Malherbe in forum ASP.NET Web Services
    Replies: 1
    Last Post: June 13th, 08:44 AM
  2. Replies: 2
    Last Post: October 21st, 06:26 PM
  3. Replies: 28
    Last Post: October 21st, 04:48 PM
  4. Clone problem, samples from "+" instead of my mouse cursor
    By dave in forum Adobe Photoshop Elements
    Replies: 0
    Last Post: August 17th, 07:15 PM
  5. Change the "web hand" cursor in normal arrow cursor?
    By FB1976 in forum Adobe Dreamweaver & Contribute
    Replies: 1
    Last Post: July 9th, 07:15 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139