SQL Server Cursor Example

Problem

In my T-SQL code I always use set based operations. I have been told these
types of operations are what SQL Server is designed to process and it should be
quicker than serial processing. I know cursors exist but I am not sure how
to use them. Can you provide some cursor examples? Can you give any
guidance on when to use cursors? I assume Microsoft created them for a reason
so they must have a place where they can be used in an efficient manner.

Solution

In some circles cursors are never used, in others they are a last resort and
in other groups they are used regularly. In each of these camps they have
different reasons for their stand on cursor usage. Regardless of your stand
on cursors they probably have a place in particular circumstances and not in others.
So it boils down to your understanding of the coding technique then your understanding
of the problem at hand to make a decision on whether or not cursor based processing
is appropriate or not. To get started let's do the following:

SQL Server Cursor Analysis

The analysis below is intended to serve as insight into various scenarios where
cursor based logic may or may not be beneficial:

Online Transaction Processing (OLTP) - In most OLTP environments, SET based
logic makes the most sense for short transactions. Our team has run into
a third party application that uses cursors for all of its processing, which
has caused issues, but this has been a rare occurrence. Typically, SET
based logic is more than feasible and cursors are rarely needed.

Reporting - Based on the design of the reports and the underlying design,
cursors are typically not needed. However, our team has run into reporting
requirements where referential integrity does not exist on the underlying database
and it is necessary to use a cursor to correctly calculate the reporting values.
We have had the same experience when needing to aggregate data for downstream
processes, a cursor based approach was quick to develop and performed in an
acceptable manner to meet the need.

Serialized processing - If you have a need to complete a process in serialized
manner, cursors are a viable option.

Administrative tasks - Many administrative tasks need to be executed in
a serial manner, which fits nicely into cursor based logic, but other system
based objects exist to fulfill the need. In some of those circumstances,
cursors are used to complete the process.

Large data sets - With large data sets you could run into any one or more
of the following:

Cursor based logic may not scale to meet the processing needs.

With large set based operations on servers with a minimal amount of
memory, the data may be paged or monopolize the SQL Server which is time
consuming can cause contention and memory issues. As such, a cursor
based approach may meet the need.

Some tools inherently cache the data to a file under the covers, so
processing the data in memory may or may not actually be the case.

If the data can be processed in a staging SQL Server database the impacts
to the production environment are only when the final data is processed.
All of the resources on the staging server can be used for the ETL processes
then the final data can be imported.

SSIS supports batching sets
of data which may resolve the overall need to break-up a large data
set into more manageable sizes and perform better than a row by row approach
with a cursor.

Depending on how the cursor or SSIS logic is coded, it may be possible
to restart at the point of failure based on a
checkpoint or marking each
row with the cursor. However, with a set based approach that may not
be the case until an entire set of data is completed. As such, troubleshooting
the row with the problem may be more difficult.

SQL Server Cursor Alternatives

Below outlines alternatives to cursor based logic which could meet the same needs:

Next Steps

When you are faced with a data processing decision determine where you stand
with SQL Server cursor usage. They may or may not have a place in your
application or operational processes. There are many ways to complete
a task, so using a cursor could be a reasonable alternative or not. You
be the judge.

If you run into issues with another coding technique and need to get something
done quickly, using a cursor may be a viable alternative. It may take
longer to process the data, but the coding time might be much less. If
you have a one time process or nightly processing, this could do the trick.

If cursors are shunned in your environment, be sure to select another viable
alternative. Just be sure the process will not cause other issues.
As an example, if a cursor is used and millions of rows are processed will this
potentially flush all of the data from cache and cause further contention?
Or with a large data set will the data be paged to disk or written to a temporary
directory?

As you evaluate a cursor based approach versus other alternatives make a
fair comparison of the techniques in terms of time, contention and resources
needed. Hopefully these factors will drive you to the proper technique.

Last Update: 2017-01-31

About the author

Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an Edgewood Solutions SQL Server Consultant, MSSQLTips.com co-founder and Baltimore SSUG co-leader.

Open Cur1Fetch next from Cur1 into @EmpSalarywhile @@FETCH_STATUS=0begin update #RES Set EmpCount=EmpCount+1 where @EmpSalary between MinSalary and MaxSalary Fetch next from Cur1 into @EmpSalary endclose cur1deallocate cur1

I am trying to create a stored procedure in SQL Server that queries data elements from several database tables. I have the logic that collects the data in place, but the result set must be consumed by a PL/SQL peocedure. How can I define an output variable in SQL Server that has a set field structure, and a variable number of rows, AND has an matching variable structure in PL/SQL that can correctly interpret it? I need the variable definitions on both platforms. Thank you.

The first thing I would note is replacing a cursor with a while loop is a really odd thing to do as they are exactly the same thing.

The second thing I would note is that Microsoft allows you to write a cursor without utilizing any of the arguments. Unfortunately most queries would execute quicker if some of the arguments were used. If would suggest using the LOCAL argument if the scope of the cursor does not extend past the current stored procedure or trigger or batch. If would also suggest using the FORWARD_ONLY argument which will optimise the query if the cursor only fetches from the first to the last record.

I need to insert column from one table to another table. I have have to add this to ssis package , so it update everyday if new column found. If I run this code, it keeps adding and duplicating same thing over and over. How do I just add once without duplicating.

INSERT

INTO

--SELECT * FROM

[DP]

.

[Datamart_Snapshots_Test]

(

[RS_Flag]

,

[FCST_Season]

,

RowID

-- Need to have new row row id with increment

,

Snapshot_Date,

Bus_Sesn

,

Gate

)

(

SELECT

/*New Fields*/

'Y'as

RS_Flag

,dmrsu.Buying_SeasonAS

FCST_Season

,''

-- need new increment row id

,

Snapshot_Date

/*Existing Fields*/

,dmrsu.

[Bus_Sesn]

,dmrsu.[Gate]

FROM

(

/*Sub Query to obtain only RS records from "current" data*/

SELECTrsu.Buying_Season,dm2

.*

,'Post-RLF'asGate

/*given by some input by stored procedure, how do I pull all gate value?*/

Thanks for the feedback Jeremy. I ended up rewriting the code without a cursor. I'm sure there are many ways to do this, but I ended up creating a temp table for every month of the year. Populated each table with the data for that month from my Inventory table, then did a big join on the item numbers to get the final result set. Now the report runs in about 30 seconds. Removing the cursor decreased the execution time by about 66%.

I agree on the index portion of your comment. I suspect you have a decent sized set of data based on the processing times.

In terms of the logic, on one side of the coin if your current logic is working for you, then keep it up. On the other side of the coin, you could probably re-write the logic and just use SELECT statements with GROUP BY logic or a CTE. You would have to test the logic to see the performance and time gains. Maybe consider this type of logic for your next set of reports, unless you have the time to jump in and make changes.

I'm a report writer for my organization and am getting some flak for including a cursor in some of my reports. I'm sure that I could rewrite the report to avoid using a cursor, but I would like some feedback to know if this particular report is a an example of an apporpriate use of a cursor. To process a years worth of data takes about 1 minute and 30 seconds.

BTW. I have found that a cursor performs 100 x faster when you are pulling from an indexed column, thus I create an index on the temp table for the column I'm using the cursor on. Before adding the index, the report would take over an hour to run.

Here is the pseudo code.

Get Item sales and inventory data for an entire year and use it to populate a temporary table called #Inventory.

Create Index on #Inventory Table for ItemNumber

User cursor loop for each Item Number in the #Inventory table to calculate monthly data from #Inventory.

Put results of calculation in a 2nd temporary table called #Results

After all item numbers are processed do final clean up and updates on #Result table and return the select * from #Results

Something like the following would avoid cursors, and use just one variable. It is not very sophisticated, efficency could probably be improved. But, this is good enough for an example of avoiding RBAR (& cursors). It would help if there were usable indexes on a few of the columns too, but I've skipped that for an example.

NOTES:

1. % gives you a sequence that starts at 1, cycles through and ends in 0. Hence the -1 for [Assigned], to make te 2 sequences align.

2. You could make it more complex, to do the "round-robin assignment" in alphabetical order - bearin in mind above. Or you could replace % with a / and making a couple of other adjustments to get an in order list. But, % is simpler and hence a more reliable "assignment function".

I'm trying to do an automated assigment process. What I've is a list of loan processors:

Loan Processors

Mel

Nathan

Scott

Every night we receive new loans for processing. In my query for the new loans I want to walk through each loan and assign a processor so my result set so my result set looks something like this when done each evening:

New Loans

Assigned To

123456

Mel

223456

Nathan

323456

Scott

423456

Mel

523456

Nathan

623456

Scott

723456

Mel

I've reied a plain CASE statement and that won't work. This appears to me to require several cursors and variables. Can someone please post a script that would accomplish this task? Thank you

please tell me iam working in the production support in using only one DQl tool for excuting sql query's then looking for a job on sql mean sql technical support or prodction support but i know very well oracle pl/sql query's i don't know iam eligible for sql & unix prodcution support.please let know please clarify....

I am not sure I understand your question 100%, but it sounds as if your SELECT statement that populates the cursor is incorrect. If you just run the SELECT statement independent of the cursor, is it correct?

Now, I am declaring cursor for table, in a row by row fetch I am inserting a data that qualifies the select statement while declaring cursor.

For e.g. if my select statement gave me 4 records for salary > 1000 now for each of these 4 record I inserted one record with salary 1000+ in the same table then the records inserted newly should be 4. so the total records for salary > 1000 should be 8 after the loop ends.

But this is not the case. The loop executes for random numbers in my case 886 to 122122 times.

Please suggest how to make sure Rows selected for cursor do not change with example.

Thank you Scott C. for the recommendation. I ended up copying all columns from the table and creating a macro to do similarly to what you recommended. It did the job pretty well. The sad thing is it seems no one knows for sure whether or not SELECT * is possible. If it is, how.

I'm not convinced that a SELECT * cursor for a 100+ column table is absolutely necessary, but if you're determined to do it and don't like to type then let the Object Browser help you out.

Right-click on the table in Object Browser and select "Script Table as CREATE To New Query Editor Window".Replace everything down to the CREATE TABLE line with DECLARE.Delete everything after the last column definition (indexes, constraints, triggers, permissions, etc). Delete the trailing comma, if any, on the last line.Use Replace All with Regular Expressions to turn the column definitions into variable declarations.Get rid of all brackets: replace "\[{[a-z0-9_]+}\]" with "\1".Prefix column names with @: replace "\t" with "\[email protected]".Remove NULL specifications: replace "{ NOT}* NULL" with "" (nothing).Manually clean up any leftovers: IDENTITY, computed column expressions, TIMESTAMP declarations. Fix any variable names that still have square brackets.

Add your basic cursor template: (the lines SELECT and INTO have a trailing space)

DECLARE xyz CURSOR FAST_FORWARD FORSELECT FROM dbo.table

FETCH NEXT FROM xyzINTO

In the object browser, click the + beside your table to expose the sub-items. Left-click on the word "Columns" and drag-and-drop to the SELECT and INTO lines.

Turn the column names in the FETCH line into variables: replace " " (a single space) with " @" ("Use regular expressions" can be turned off).Repeat any manual fixes made above to screwy column names.

I'm not sure if you've received my post as I don't see it above. Here it is again and I appologize if I've posted it twice.

I have to write a process that reads Employee records, update other tables and insert into another table for other processes. Before updates and inserts can be done, I'd have to do a lot of data manipulations. I guess my question should be:

If I MUST use select * from employee cursor, how would I go about referencing a column (i.e. employee.empID) from the cursor without having to declare vairables and fetch into them?

As you can see from above, I've named my cursor emp_cur and used select * from employee. When I want to reference a column, I qualify the column name with the cursor name (i.e. emp_cur.empID), and I do not have to use variables.

To take a step back, what are you ultimately trying to do? Issue a simple UPDATE, INSERT or DELETE statement? ETL process? Data import or export?

If I understand your first question correctly, you would need to reference each column when you create a variable, populate the cursor, iterate the row set if you are processing each column in some sort of manner.

If I understand your second question correctly, you would need to reference the variable @empID.

Thank you Tim and Jeremy for your responses. The code I submitted was only an example. I'd really prefer to use SELECT * statement as I have to use and process more than 100 columns in the table. I really don't want to list them all multiple times (DECLARE, SELECT, FETCH NEXT (twice)).

When I referred to empID as emp_cur.empID, I got the error "SQL Server Database Error: The name "emp_cur.empID" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Unless you absolutely have to use every column in the table for the cursor activity I would avoid SELECT * statements. You should be able to reference the column by name. Did you get an error when you attempted to execute the code above?

My first inclination would be that the query you use to populate the cursor is not getting the correct data. If you just run the SELECT query in the 'Declare Cursor' portion of the code, are you getting the correct data?

We are using a script very similar to this. It uses exec msdb.dbo.sp_send_dbmail to email our clients appointment confirmations.

A sql view has been written that calls the data set correctly. Some clients have two appts the same day for different reasons and at different times. Most emails are sent correctly, with the correct data.

But these records for clients that have two appts are sending only the data for one of the appts, and then sending it twice. Any ideas? Is there a flush command that can solve this?

I think another point to mention is to limit the amount of information obtained for use with the cursor. I see a number of people who select everything from a table even though they only need two fields.

One thing that should be added... if you're going to use a cursor and the results do not have to be sensitive to change and you don't need to move back and forth within the set, then look at using the FAST_FORWARD option which is a low overhead, read only, forward only type with no tempdb overhead. It runs through the resultset sequentially.