TSQL - A question regarding cursors

Posted on 2014-08-19
Last Modified: 2014-08-27

I have a stored procedure that creates a cursor and loops though a table on a specific order.

What happens if i insert a new record?  Can that appear in the result when i get the next record if i insert it so it's order is directly after the one i am working with?

Basically is the set of records in returned by the cursor difined at the time the cursor is opened, or is it dynamic so changes in the database can result in these new records appearing?

I am looping through a table looking for missing records and inserting them when i find (or dont find them!).  I dont want the newly insert record to appear when i get the next record.
Question by:soozh
    LVL 12

    Accepted Solution

    You can define the cursor as KEYSET:


    it specifies that the order of rows in the cursor are fixed when the cursor is opened.

    You can also use STATIC or INSENSITIVE arguments instead of KEYSET. it depends on your scenario but I think KEYSET is better a solution for your problem.

    LVL 25

    Assisted Solution

    Cursors on a table that you actualy change on the fly are unpredictable. unless you make it a static cursor

    And always when I see a cursor the question pops up... is that cursor necessary?
    LVL 31

    Expert Comment

    >>I am looping through a table looking for missing records and inserting them when i find (or dont find them!).<<
    What determines they are missing and what are you inserting if they are? As jogos mentions, "Is that cursor necessary?" You might be able to accomplish what you want using the merge function. Some sample data and desired results would be most helpful.
    LVL 25

    Expert Comment

    Finding missing sequence numbers without cursor some examples on this link

    Examples are select's. If you make that select feed an insert statement then you can fill your gaps. Example of a CTE-insert
    LVL 31

    Expert Comment

    Can you post your stored procedure?

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
    In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now