[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

TSQL - A question regarding cursors

Posted on 2014-08-19
5
Medium Priority
?
170 Views
Last Modified: 2014-08-27
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.
0
Comment
Question by:soozh
  • 2
  • 2
5 Comments
 
LVL 12

Accepted Solution

by:
Habib Pourfard earned 1000 total points
ID: 40271889
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
 
LVL 25

Assisted Solution

by:jogos
jogos earned 1000 total points
ID: 40271964
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
 
LVL 32

Expert Comment

by:awking00
ID: 40272445
>>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
 
LVL 25

Expert Comment

by:jogos
ID: 40278464
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
 
LVL 32

Expert Comment

by:awking00
ID: 40278837
Can you post your stored procedure?
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

867 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