Professional Web Applications Themes

Without Cursors? - Microsoft SQL / MS SQL Server

Hi, I am working on Stored Procedure in MSSQL Server. How can I navigate throught a table row by row without using cursor within stored procedure? Uddhab...

  1. #1

    Default Without Cursors?

    Hi,

    I am working on Stored Procedure in MSSQL Server. How can I navigate
    throught a table row by row without using cursor within stored procedure?

    Uddhab


    Uddhab Guest

  2. #2

    Default Re: Without Cursors?

    Hi Uddhab,

    That is quite easy to do if you have a primary key on the table:

    Example:

    USE Northwind
    DECLARE empid INT
    SET empid = 0

    WHILE 1=1
    BEGIN
    SELECT TOP 1 firstname FROM employees
    WHERE employeeid > empid
    ORDER BY employeeid ASC
    IF ROWCOUNT = 0 BREAK
    SET empid = (SELECT TOP 1 employeeid FROM employees
    WHERE employeeid > empid
    ORDER BY employeeid ASC)
    END


    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Uddhab R. Pant" <com> wrote in message
    news:phx.gbl... 


    Jacco Guest

  3. #3

    Default Re: Without Cursors?

    Don't! Write a set-based query which will generally perform much faster than
    the equivalent cursor.

    Why do you think you need row by row processing? If you describe your
    requirement maybe someone can give more specific advice.

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

    "Uddhab R. Pant" <com> wrote in message
    news:phx.gbl... 


    David Guest

  4. #4

    Default Re: Without Cursors?

    Well you can't but maybe if you explain why you think you need to navigate
    row by row we can come up with an alternative set based method.

    --

    Andrew J. Kelly
    SQL Server MVP


    "Uddhab R. Pant" <com> wrote in message
    news:phx.gbl... 


    Andrew Guest

  5. #5

    Default Re: Without Cursors?

    Hi Jacco,
    Is it possible without a PK.
    I have a same scenario but no PK, so do I need to have a
    Pk or any workaround.
    pls let me know.
    Thanks.

     
    the table: [/ref]
    can I navigate [/ref]
    within stored procedure? 
    >
    >
    >.
    >[/ref]
    sam Guest

  6. #6

    Default Re: Without Cursors?

    That depends what you are trying to do. Why do you want to return individual
    rows? Post DDL (CREATE TABLE statements) and sample data (INSERT statements)
    and maybe someone can help.

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



    David Guest

Similar Threads

  1. Cursors
    By flashster in forum Macromedia Director Lingo
    Replies: 8
    Last Post: December 12th, 01:47 PM
  2. Cursors again
    By Eric in forum Mac Programming
    Replies: 28
    Last Post: November 12th, 12:17 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