TSQL - A question regarding cursors

Hej,

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.
soozhAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Habib PourfardSoftware DeveloperCommented:
You can define the cursor as KEYSET:

DECLARE cursor_name CURSOR 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.

MSDN
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jogosCommented:
Cursors on a table that you actualy change on the fly are unpredictable. unless you make it a static cursor http://technet.microsoft.com/en-us/library/ms191286(v=sql.105).aspxal

And always when I see a cursor the question pops up... is that cursor necessary?
0
awking00Commented:
>>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.
0
jogosCommented:
Finding missing sequence numbers without cursor some examples on this link
http://sql-developers.blogspot.be/2012/10/how-to-find-missing-identitysequence.html

Examples are select's. If you make that select feed an insert statement then you can fill your gaps. Example of a CTE-insert
http://tsqlprincess.wordpress.com/2011/04/08/insert-this-insert-that-inserting-with-ctes/
0
awking00Commented:
Can you post your stored procedure?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.