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 included them in SQL Server 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, they probably have a place in particular circumstances and not in others. 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:

Look at an example cursor
Break down the components of the cursor
Provide additional cursor examples
Analyze the pros and cons of cursor usage

Let's first provide a SQL Server Cursor example then answer all of the pertinent questions.

SQL Server Cursor Example

Here is an example SQL Server cursor from this tip Simple script to backup all SQL Server databases where backups are issued in a serial manner:

DECLARE @name VARCHAR(50) -- database name 
DECLARE @path VARCHAR(256) -- path for backup files 
DECLARE @fileName VARCHAR(256) -- filename for backup 
DECLARE @fileDate VARCHAR(20) -- used for file name 

SET @path = 'C:\Backup\' 

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 

DECLARE db_cursor CURSOR FOR 
SELECT name 
FROM MASTER.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb') 

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name  

WHILE @@FETCH_STATUS = 0  
BEGIN  
      SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 
      BACKUP DATABASE @name TO DISK = @fileName 

      FETCH NEXT FROM db_cursor INTO @name 
END 

CLOSE db_cursor  
DEALLOCATE db_cursor

What is a SQL Server Cursor

A SQL Server cursor is a set of T-SQL logic to loop over a predetermined number of rows one at a time. The purpose for the cursor may be to update one row at a time or perform an administrative process such as SQL Server database backups in a sequential manner. SQL Server cursors are used for Development, DBA and ETL processes.

How to Write a Cursor in SQL Server

Creating a SQL Server cursor is a consistent process. Once you learn the steps you are easily able to duplicate them with various sets of logic to loop through data. Let's walk through the steps:

Declare your variables (file names, database names, account numbers, etc.) that you need in the logic and initialize the variables.
- This logic would be updated based on your needs.
Declare cursor with a specific name (i.e. db_cursor in this tip) that you will use throughout the logic along with the business logic (SELECT statement) to populate the records the cursor will need. The cursor name can be anything meaningful. This is immediately followed by opening the cursor.
- This logic would be updated based on your needs.
Fetch a record from cursor to begin the data processing.
- NOTE - There are an equal of number of variables declared for the cursor, columns in the SELECT statement and variables in the Fetch logic. In the example in this tip there is only one variable, one column selected and variable fetched, but if five pieces of data were needed for the cursor then five variables would need to be selected and fetched as well.
The data process is unique to each set of logic. This could be inserting, updating, deleting, etc. for each row of data that was fetched. This is the most important set of logic during this process that is performed on each row.
- This logic would be updated based on your needs.
Fetch the next record from cursor as you did in step 3 and then step 4 is repeated again by processing the selected data.
Once all of the data has been processed, then you close cursor.
As a final and important step, you need to deallocate the cursor to release all of the internal resources SQL Server is holding.

From here, check out the examples below to get started on knowing when to use SQL Server cursors and how to do so.

Cursor in SQL Server

Based on the code and explanations above, let's break down the SQL Server cursor example and notate which sections would need to be updated when using this code.

-- 1 - Declare Variables
-- * UPDATE WITH YOUR SPECIFIC CODE HERE *
DECLARE @name VARCHAR(50) -- database name 
DECLARE @path VARCHAR(256) -- path for backup files 
DECLARE @fileName VARCHAR(256) -- filename for backup 
DECLARE @fileDate VARCHAR(20) -- used for file name 

-- Initialize Variables
-- * UPDATE WITH YOUR SPECIFIC CODE HERE *
SET @path = 'C:\Backup\' 

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 

-- 2 - Declare Cursor
DECLARE db_cursor CURSOR FOR 
-- Populate the cursor with your logic
-- * UPDATE WITH YOUR SPECIFIC CODE HERE *
SELECT name 
FROM MASTER.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb') 

-- Open the Cursor
OPEN db_cursor

-- 3 - Fetch the next record from the cursor
FETCH NEXT FROM db_cursor INTO @name  

-- Set the status for the cursor
WHILE @@FETCH_STATUS = 0  
 
BEGIN  
	-- 4 - Begin the custom business logic
	-- * UPDATE WITH YOUR SPECIFIC CODE HERE *
   	SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 
  	BACKUP DATABASE @name TO DISK = @fileName 

	-- 5 - Fetch the next record from the cursor
 	FETCH NEXT FROM db_cursor INTO @name 
END 

-- 6 - Close the cursor
CLOSE db_cursor  

-- 7 - Deallocate the cursor
DEALLOCATE db_cursor

Explanation of Cursor Syntax in SQL Server

Based on the example above, cursors include these components:

DECLARE statements - Declare variables used in the code block
SET\SELECT statements - Initialize the variables to a specific value
DECLARE CURSOR statement - Populate the cursor with values that will be evaluated
- NOTE - There are an equal number of variables in the DECLARE CURSOR FOR statement as there are in the SELECT statement. This could be 1 or many variables and associated columns.
OPEN statement - Open the cursor to begin data processing
FETCH NEXT statements - Assign the specific values from the cursor to the variables to match the DECLARE CURSOR FOR and SELECT statement
- NOTE - This logic is used for the initial population before the WHILE statement and then again during each loop in the process as a portion of the WHILE statement
WHILE statement - Condition to begin and continue data processing
BEGIN...END statement - Start and end of the code block
- NOTE - Based on the data processing, multiple BEGIN...END statements can be used
Data processing - In this example, this logic is to backup a database to a specific path and file name, but this could be just about any DML or administrative logic
CLOSE statement - Releases the current data and associated locks, but permits the cursor to be re-opened
DEALLOCATE statement - Destroys the cursor

Why Use a Cursor in SQL Server

Although using an INSERT, UPDATE or DELETE statement to modify all of the applicable data in one transaction is generally the best way to work with data in SQL Server, a cursor may be needed for:

Iterating over data one row at a time
Completing a process in a serial manner such as SQL Server database backups
Updating data across numerous tables for a specific account
Correcting data with a predefined set of data as the input to the cursor

When to Use a SQL Server Cursor

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 (INSERT, UPDATE or DELETE on applicable rows) 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 a serialized manner, cursors are a viable option.
Administrative tasks - Many administrative tasks such as database backups or Database Consistency Checks 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 Types

Simon Liew has written a detailed technical tip on five Different Ways to Write a Cursor in SQL Server which includes the following:

Most Common SQL Server Cursor Syntax
SQL Server Cursor as a Variable
SQL Server Cursor as Output of a Stored Procedure
SQL Server Cursor Current Of Example
SQL Server Cursor Alternative with a WHILE Loop

This tip provides sample code that can be used to expand your SQL Server cursor options beyond the syntax in this tip.

SQL Server Cursor Alternatives

Learn more about SQL Server Cursors and alternatives:

How to Avoid Cursor in SQL Server

Below outlines SQL Server T-SQL coding options to avoid SQL Server Cursors:

Set based logic
- INSERT or SELECT INTO or INSERT... SELECT to add records to a table as a single transaction
- UPDATE to modify one or many rows in a single transaction
- DELETE or TRUNCATE to remove records from a table
- MERGE branching logic to INSERT, UPDATE or DELETE data based on criteria
Consider SQL Server Integration Services (SSIS) to loop through data primarily for data extraction, transformation and loading processes between databases
WHILE command to loop to over records in a sequential manner
- Optimize Large SQL Server Insert, Update and Delete Processes by Using Batches
COALSCE command to process NON-NULL values
- The Many Uses of Coalesce in SQL Server
sp_MSforeachdb SQL Server system stored procedure to loop over each database on an instance
- Run The Same SQL Command Against All SQL Server Databases
sp_MSforeachtable SQL Server system stored procedure to loop over each table in a database
- Iterate through SQL Server database objects without cursors
CASE expression which can include some branching logic to process data with a SELECT statement
- Using the CASE expression instead of dynamic SQL in SQL Server
Repeat a batch with the GO command
- Executing a T-SQL batch multiple times using GO

Difference Between While Loop and Cursor in SQL Server

Daniel Farina has written an interesting article (SQL Server Loop through Table Rows without Cursor) comparing the SQL Server While Loop and Cursors. He covers the following:

Using a While Loop Instead of Cursors in SQL Server
Pros and Cons of Using Cursors to Iterate Through Table Rows in SQL Server
Pros and Cons of Using a While Loop to Iterate Through Table Rows in SQL Server
Example of a Basic Cursor to Loop through Table Rows in SQL Server
Example of a Basic While Loop to Cycle through Table Rows in SQL Server

The code samples in this tip are valuable to illustrate the differences between cursors in SQL Server and the While Loop.

Additional SQL Server Cursor Examples

In the example above, backups are issued via a cursor. Check out these other tips that leverage cursor-based logic:

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. 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.

About the author

Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor’s degree from SSU and master’s from UMBC.

View all my tips

Article Last Updated: 2020-12-31

Comments For This Article

Wednesday, September 7, 2022 - 2:33:06 PM - Brock Roberts	Back To Top (90450)
Great article. Well written. Useful. I get frustrated by some people who like to pontificate on some deep CS philosophy about T-SQL cursors being bad. I just need to loop over records and this works. Thanks again for a great article that is to the point and easy to read.

Wednesday, August 18, 2021 - 9:20:00 AM - Jeremy Kadlec	Back To Top (89142)
Nikhil, Good morning. I would check out Aaron Bertrand's tip - https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/. He has a 30 year period in his tip, but you can just change the year parameter as needed. HTH. Thank you, Jeremy Kadlec Community Co-Leader

Wednesday, August 18, 2021 - 3:26:11 AM - Nikhil	Back To Top (89140)
I have to create a calendar starting from jan 2020 and fiscal calendar starting from july 2020 and it should display the data of next 5 years we should pass parameter.using cursors,i am unable to get it.

Wednesday, July 28, 2021 - 10:19:56 AM - Jeremy Kadlec

Moum,

I hope this tip helped you.

I would also encourage you to check out these resources:

https://www.mssqltips.com/sqlservertip/6308/different-ways-to-write-a-cursor-in-sql-server/

https://www.mssqltips.com/sqlservertip/6148/sql-server-loop-through-table-rows-without-cursor/

https://www.mssqltips.com/sqlservertip/1414/run-same-command-on-all-sql-server-databases-without-cursors/

https://www.mssqltips.com/sqlservertutorial/2908/sql-server-convert-cursor-to-set-based/

HTH.

Thank you,
Jeremy Kadlec
Community Co-Leader

Wednesday, July 28, 2021 - 5:15:16 AM - moum	Back To Top (89062)
Explaination with the most complex example

Monday, May 10, 2021 - 12:56:02 PM - Joe F Celko

Actually, you don't really need cursors. They exist in SQL because the original SQL implementations were built on top of existing filesystems. SQL was a weak language; we had a joke on the ANSI Standards committee that SQL stood for "Scarcely Qualifies as a Language" while we were working on it.

If you become a computer science major, you'll run into two things, and prove their equivalency. The first is finite state automaton and the second is primitive recursive functions. The first one is the abstract mathematical model for what we think of as a computer. There's usually a discussion about how many push down stacks a machine has and some proofs as to what kind of computations various versions of these abstract machines can compute. Primitive recursive functions are more like SQL; they consist of functions that can be recursively nested inside each other. We need a selection (if – then – else), a loop (while – do) and the concatenation (begin – end) to do the same thing as we could do with our finite state automaton. The proof is really quite nice and boring as hell, something only a real nerd would love. But the formal proof shows that you don't need cursors.

Back in the real world, Sybase used to have an example their training manuals that used a bookstore database. They had a sale which would raise the price of cheap books (cost <= $25) without telling the customers and make a big deal about reducing the price of expensive books (cost > $25). The original UPDATE statements in the training manual looked like this:

BEGIN
UPDATE Books -- expensive books
SET book_price = book_price * 0.90
WHERE book_price >= 25.00;
UPDATE Books -- cheap books
SET book_price = book_price * 1.10
WHERE book_price < 25.00;
END;

The manual then gave some sample data and things look fine until you got to a book whose price was on the cusp. The UPDATE statements would reduce the price, then increase the price. If you switch to the UPDATE statements around, you got a little different version of the same problem. The answer given back in those days was to use a cursor and change the prices row by row.

Today we have the CASE expression, which corresponds to a basic primitive recursive function called selection, so we can write this as:

UPDATE Books --all books
SET book_price =
CASE WHEN book_price >= 25.00 THEN = book_price * 0.90
WHEN book_price < 25.00 THEN = book_price * 1.10
ELSE NULL

In working with SQL over 30 years, I've written five cursors total. Today, I know I could have avoided at least three of them simply with the CASE expression.

Sunday, July 26, 2020 - 2:38:37 PM - mouzzel dev	Back To Top (86192)
I'm still using the database backup script defined above (with a few small modifications). Always come back to read and learn additional parts to use the cursors properly. Thank you so much.

Monday, April 27, 2020 - 3:47:17 PM - Jeremy Kadlec

Jason,

Thank you for the feedback.

In this article we provided a few different alternatives to using SQL Server Cursors. One of the blessing and curses of SQL Server is that there are numerous ways to solve a problem. Knowing all of your options is important to select the best solution.

Thank you,
Jeremy Kadlec
Community Co-Leader

Friday, April 24, 2020 - 10:05:08 AM - Jason

Or we could just follow the advice of ALL the experts and not use a cursor at all...

instead of a cursor we could just build the statements dynamically and execute them. There's no user input here so injection is mitigated

DECLARE 
  @SQL NVARCHAR(max),
  @path NVARCHAR(2048) = 'C:\Temp\',
  @suffix NVARCHAR(32) = concat('_',convert(VARCHAR,sysdatetime(),112),'T',replace(convert(VARCHAR,sysdatetime(),108),':',''),'.bak')
SELECT @SQL = coalesce(@SQL,'') + concat('BACKUP DATABASE ', quotename(name), 
' TO DISK = N',quotename(concat(@path, name, @suffix),''''),' WITH STATS = 10 ')
FROM sys.databases
WHERE database_id > 4
-- Printing so that you can see that every database backup 
-- statement is generated and without a CURSOR
PRINT @SQL
EXEC sys.sp_executesql @SQL

Tuesday, February 19, 2019 - 6:07:16 AM - padmaraj	Back To Top (79061)
How do you use a cursor to insert into a table same data multipal time

Saturday, January 5, 2019 - 10:55:31 AM - Jeremy Kadlec

Nikola,

Yes - SQL Server Cursors are designed for row by row operations.

Thank you,
Jeremy Kadlec
Community Co-Leader

Saturday, January 5, 2019 - 10:13:41 AM - nikola	Back To Top (78640)
Whether SQL Server cursors, unlike MySQL cursors, can access each row individually or access the rows the same?

Wednesday, October 3, 2018 - 10:08:12 AM - Dan N

i like to use a var for a cursor

declare @c cursor

set @c = cursor fast_forward read_only for Select column from table

one benefit is that if you just happen to forget to close and deallocate it will be handled automatically and unlike regular cursor syntax it's clear that it's something that has been declared

Wednesday, October 3, 2018 - 9:07:50 AM - Jeremy Kadlec

Joe,

Thank you for the feeback and history.

Thank you,
Jeremy Kadlec

Tuesday, October 2, 2018 - 2:15:25 PM - Joe Celko

Microsoft did not invent cursors as you said in this article. They were part of the ANSI X3H2 standards at the beginning of SQL. We had them because the original relational databases were built on top of existing filesystems. If you have ever worked with an IBM magnetic tape file system, you would immediately recognize that all of the cursor commands that we had in SQL for allocating, deallocating and using a cursor were mapped command for command from the tape files.

Microsoft version of cursors was based on the UNIX version of magnetic tape drives. This is why the first versions of cursors in Sybase and Microsoft SQL Server were based on UNIX files.

Later, the cursors grew to be much more complicated. I believe it was Mr. David McGovern who said, "a committee never met a feature it didn't like" and he was correct. The ANSI/ISO standard version is much more elaborate Microsoft has.

Jim Grey used to say in the early days of SQL, "we had no idea what the hell we were doing!" and he was right :-). Essentially, our early SQL engines were so weak the only way we could get any work done was to expose the underlying file system and give access through our language into the file system underneath it all.

However, that's not the situation today. The SQL standard has been greatly expanded since the SQL – 86 standard, and includes a pretty good declarative programming language. I hope that we will eventually deprecate everything to do with cursors in the SQL standard. This is not likely, because you need to preserve your antique features to make sure that old code will still run.

Thursday, September 27, 2018 - 9:33:23 PM - CodinBogin	Back To Top (77760)
Great piece of explanatory and consice code, thanks.

Tuesday, April 3, 2018 - 9:19:02 AM - Rick Dobson

Depending on the objectives of an analysis, the lead and lag functins may offer an interesting alternative to using cursors.

I have not performed any testing, but my guess is that these functions may provide faster performance for analyses to which they apply.

Wednesday, March 7, 2018 - 9:54:55 AM - Montey

Thanks for the post. It was very helpful.

Friday, July 8, 2016 - 9:09:15 AM - Bryan

Nice overall coverage of using a cursor! Good job.

Friday, July 31, 2015 - 4:31:16 AM - Enrique	Back To Top (38332)
but the same operation you can make it with a simple "While" why do you use a "Cursor"

Monday, June 15, 2015 - 12:23:16 PM - Zain	Back To Top (37922)
nice example

Saturday, May 30, 2015 - 7:26:37 AM - Mustafa EL-Masry

I see it will be more easy if we used the below T-SQL it will give us all the databases not one DB with every thing we need

SELECT D.name As Database_Name,M.name Database_File_Name, M.physical_name AS current_file_location

FROM sys.master_files AS M

inner join Sys.Databases As D

ON M.database_id = D.database_id

Friday, January 9, 2015 - 11:17:51 AM - Simon2309

How can I easy copy data from table A to table B?

Referenc between both tables is Articlenumber.

In table A is the information of the catalogpage of each article.

In table B is the new Preis for each article. Now I'd like to merge this information in one table. (without cursor?)

I use MSSQL 2008 with Studio 14.

many thanks for help

Wednesday, November 26, 2014 - 1:11:47 PM - Jeremy Kadlec

Samira,

Check out the following tutorial - http://www.mssqltips.com/sqlservertutorial/2514/sql-server-insert-command/.

HTH.

Thank you,
Jeremy Kadlec
Community Co-Leader

Wednesday, November 26, 2014 - 12:26:05 AM - samira sanginian	Back To Top (35406)
hi,any boddy know who create insert proc on all column in all table of database?

Monday, November 10, 2014 - 2:33:55 PM - jouma

Can somebody please help me to get this resolved.

Write an SQL to return the count of Employees by Salary Band

Table : EmployeeSalary

Employee Salary

------------- -----------

1 21,000

2 24,000

3 30,000

4 31,000

5 32,000

6 50,000

Table: SalaryBands

ID Name MinSalary MaxSalary

---- ------- ------------- -------------

1 Tier1 20,000 24,999

2 Tier2 25,000 44,999

3 Tier3 45,000 150,000

==>

Expected Output

ID Name SalaryBands Count

---- ------- -------------

1 Tier1 2

2 Tier2 3

3 Tier3 1

My Name is jouma almchawal

the solution is

Drop Table #RES
Create TABLE #RES(ID int ,NAME varchar(50),MinSalary float,MaxSalary float,EmpCount int )
Declare @EmpSalary float
INSERT INTO #RES (ID,NAME,MinSalary,MaxSalary)
        Select [ID],[NAME],[MinSalary],[MaxSalary] FROM SalaryBands

update #RES Set EmpCount=0
Declare Cur1 Cursor For
Select SALARY from EmployeeSalary

Open Cur1
Fetch next from Cur1 into @EmpSalary
while @@FETCH_STATUS=0
begin
update #RES Set EmpCount=EmpCount+1
       where @EmpSalary between MinSalary and MaxSalary
    Fetch next from Cur1 into @EmpSalary
end
close cur1
deallocate cur1

Select * From #RES

Wednesday, October 29, 2014 - 6:40:00 PM - Conrad

Hello,

Thank you for the informative post. After reading through the comments, I would like to know if there is a time savings between using a cursor and using WHILE loops.

Thank you in advance,

Conrad

Monday, September 8, 2014 - 5:38:05 PM - sql1

Thanks for the article.. it was helpful!!

Tuesday, July 1, 2014 - 2:56:20 AM - amit

Can somebody please help me to get this resolved.

Write an SQL to return the count of Employees by Salary Band

Table : EmployeeSalary

Employee Salary

------------- -----------

1 21,000

2 24,000

3 30,000

4 31,000

5 32,000

6 50,000

Table: SalaryBands

ID Name MinSalary MaxSalary

---- ------- ------------- -------------

1 Tier1 20,000 24,999

2 Tier2 25,000 44,999

3 Tier3 45,000 150,000

==>

Expected Output

ID Name SalaryBands Count

---- ------- -------------

1 Tier1 2

2 Tier2 3

3 Tier3 1

Thursday, April 17, 2014 - 1:23:29 PM - Eileen McCabe

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.

Tuesday, March 25, 2014 - 7:41:26 AM - Jacobo	Back To Top (29864)
Thank you very much!

Friday, January 31, 2014 - 10:46:10 AM - Jeremy Kadlec

Syed,

I am not sure I understand your question 100%, but let me see if I can give you some cursor logic.

DECLARE @LocationID int
DECLARE @Message varchar(500)
DECLARE @Content varchar(500)

SET @Message = ‘Your message here’
SET @Content = ‘Your content here’

DECLARE db_cursor CURSOR FOR
SELECT DISTINCT(LocationID)
FROM Database.dbo.Table

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @LocationID

WHILE @@FETCH_STATUS = 0
BEGIN
insert into message(ID, Message, Location) values (@LocationID, @Message, @Content );

FETCH NEXT FROM db_cursor INTO @LocationID
END

CLOSE db_cursor
DEALLOCATE db_cursor

Keep in mind, I am not sure I understand your question 100%, but if the logic above works, you could solve this problem without a cursor.

Thank you,
Jeremy Kadlec
MSSQLTips.com Commnity Co-Leader

Friday, January 31, 2014 - 12:38:05 AM - syed

i have a Location table like this

id Location

1 A

2 B

3 C

and another table message like this,

id Message content

1 msg xxx

2 msg xxx

1 msg xxx

if i give "0" as input means

3 rows must be inserted in 'Message' table with the ID's in 'Location' table like this

id Message content

1 msg xxx

2 msg xxx

1 msg xxx

2 msg xxx

3 msg xxx

and if i had one more Location means (ie)

id Location

1 A

2 B

3 C

4 C

and if i give "0" as input to my procedure means

4 rows must be inserted in 'Message' table with the ID's in 'Location' table like this

id Message content

1 msg xxx

2 msg xxx

1 msg xxx

2 msg xxx

3 msg xxx

4 msg xxx

now i am doing like this

BEGIN

IF (locationid=0) THEN

insert into message(Id) values (1);

insert into message(Id) values (2);

insert into message(Id) values (3);

ELSE

--do nothing

END IF;

END

this works, but when i increase one more location means, i have to insert extra query for it.

i dont want to insert extra query, whenever i increase my Location table.

But, i know that it can be easyly solved by using Cursor, i dont know how to use it..

Please help me to figure this problem...

Thursday, January 16, 2014 - 11:43:49 AM - Jeremy Kadlec

Bouncy B,

Thank you for the feedback. Those are great points. I will try to include them in an updated version of this tip.

Thank you,
Jeremy Kadlec
MSSQLTips.com Community Co-Leader

Thursday, January 16, 2014 - 12:43:53 AM - Bouncy B

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.

Wednesday, October 9, 2013 - 1:25:31 PM - Nina

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?*/

FROM[DP].[Datamart]

dm2

INNERJOIN[DP].[Reverse_Seasonality_Unique]

rsu

ONRSU.Selling_Season=dm2.

[Bus_Sesn]

ANDRSU.APO_Prod_Cd=dm2.

APO_Prod_Cd

ANDRSU.Plng_Ctry_Cd=dm2.

Plng_Ctry_Cd

)

dmrsu

LEFTOUTERJOIN[DP].[Datamart_Snapshots]

dms

dmrsu.[Bus_Mo]=dms.

[Bus_Mo]

ANDCAST(dmrsu.Plng_Ctry_Cdaschar(5))+dmrsu.APO_Prod_Cd=dms.

Plng_Ctry_Prodt_Cd

ANDdmrsu.Gate=dms.

Gate

WHERE

dms.Plng_Ctry_Prodt_Cdis

NULL

ANDSnapshot_DateISNULL

)

Monday, June 10, 2013 - 9:34:46 AM - Wayne

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%.

Saturday, June 8, 2013 - 6:11:42 AM - Jeremy Kadlec

@Mark,

Thank you and you too!

@Wayne,

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.

Thank you,
Jeremy Kadlec
Community Co-Leader

Monday, June 3, 2013 - 4:44:48 PM - Wayne

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

Thursday, May 9, 2013 - 1:41:59 PM - Mark Kadlec	Back To Top (23843)
Good tips.....and you have a great name!

Wednesday, March 6, 2013 - 10:23:25 AM - tani

i have 3 questions.

1. write a pl/sql prog using triggers to copy the records of an employee into a backup table when that particular record is deleted.

2. write a pl/sql block that would handle the error when a wrong employee no is given as input or when a wrong amount is given as input(with triggers and without triggers)

3. using trigger see to that an employeee's commission never reaches above 1000.

i need the answer very fast..do hlp plz

Friday, November 2, 2012 - 8:53:31 AM - Grignar Grenac

For loaning processing assignment query.

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".

CREATE TABLE loan(id INT, name NVARCHAR(256), processed BIT)
CREATE TABLE processor(id INT, name NVARCHAR(256), available BIT)

INSERT loan(id, name, processed)
VALUES(123456, 'Alice', 0),
(223456, 'Bob', 0),
(323456, 'Charles', 0),
(423456, 'Dave', 0),
(523456, 'Eric', 0),
(234243, 'Julie', 1),
(623456, 'Fran', 0),
(343434, 'Fred', 1),
(723456, 'Georgina', 0);

INSERT processor(id, name, available)
VALUES(1, 'Mel', 1),
(3, 'Andy', 0),
(5, 'Nathan', 1),
(6, 'Scott', 1);

DECLARE @avail int
SET @avail = (SELECT count(1) FROM processor WHERE available = 1)

SELECT loans.[New Loans], loans.[Loanee Name], processors.[Assigned To]
FROM (SELECT L.id [New Loans], row_number() OVER(ORDER BY L.id) % @avail [Assignee], L.name [Loanee Name]
      FROM loan L
      WHERE L.processed = 0) loans
JOIN (SELECT (row_number() OVER(ORDER BY P.name)) - 1 [Assigned], P.name [Assigned To]
      FROM processor P
      WHERE P.available = 1) processors
ON loans.Assignee = processors.Assigned
ORDER BY loans.[New Loans]

Wednesday, October 10, 2012 - 8:22:36 PM - Mel

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

Monday, September 24, 2012 - 8:11:00 AM - Vinicio Aizpurua

Tim Cullen

On your example (listed below) Isn't this same thing as using a cursor, I mean serial processing? Only difference I could see is that you are getting the data directly for the table.

Please let me know your Comments

Regards

Vinicio Aizpurua

Miami FL

TIM CULLEN's Example

Here is an example of using a non-cursor loop to get the information:

SET NOCOUNT ON;

DECLARE @Transactions TABLE

(EntryID INT IDENTITY(1,1) PRIMARY KEY, TransactionTypeID INT, TransactionType VARCHAR(150))

DECLARE @Start INT = 1, @End INT, @TransactionTypeID INT, @TransactionType VARCHAR(150)

INSERT INTO @Transactions (TransactionTypeID, TransactionType)

SELECT TransactionTypeID, TransactionType FROM dimTransactionType WITH (NOLOCK)

SELECT @End = @@ROWCOUNT

WHILE @Start <= @End

BEGIN

SELECT @TransactionTypeID = TransactionTypeID, @TransactionType = TransactionType

FROM @Transactions WHERE EntryID = @Start

PRINT @TransactionTypeID

PRINT @TransactionType

SELECT @Start += 1

CONTINUE

END

SET NOCOUNT OFF;

Thursday, September 20, 2012 - 9:48:08 AM - praveen

Dear Sir

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....

Wednesday, September 19, 2012 - 9:57:28 AM - Rajasekhar

Hi jermy,

Your contributions are very helpfull for SQLDBA's in the world.I dont know how we thanks to you.

Regards,

Rajasekhar Reddy

cell No:+919966246368

Monday, September 17, 2012 - 12:32:19 PM - gary

Hi Jeremy,

Yes this is what I am expecting my final data to look like. As per Org_ID

1/1/2008 to 2/27/2009
2/28/2009 to 3/31/2010
4/1/2012 and probably getdate()

Org_ID and StartDate and Price are the three fields I have in my table. Org_ID and StartDate combined make it unique.

I have to determine end date of the price for each Org_ID based on its start_date for different years.

Thanks

Gary

Monday, September 17, 2012 - 9:10:28 AM - Jeremy Kadlec

Gary,

I am not sure I understand your question completely. Are you expecting this to be your final data?

1/1/2008 to 2/27/2009
2/28/2009 to 3/31/2010
4/1/2012 and probably getdate()

Is there a column that unique identifies each row in this table?

Thank you,
Jeremy Kadlec

Saturday, September 15, 2012 - 12:48:05 AM - gary

ORG_ID START_DATE END_DATE

222 1/1/2008 ?

222 2/28/2009 ?

222 4/1/2010 Default Value

How Can I find the End Date of first occurence og ORG_ID. End Date of first occurence shud be one date before 2nd occurence and a default value for the last occurence. Sql Server 2000.

Tuesday, September 4, 2012 - 8:24:07 AM - Jeremy Kadlec

SOM,

Great. I am happy to hear it.

I plan on writing a tip about some of these additional features in the future.

Thank you,
Jeremy Kadlec

Tuesday, September 4, 2012 - 2:49:42 AM - SOM

Thanks Jeremy for the reply.

I got the solution to my problem and it is using STATIC CURSOR.

Below is what I was trying without static cursor.

Declare @name varchar(50)

Declare @level int

Declare @id int

Declare Cur_xx CURSOR FOR SELECT name,D_level FROM xx WHERE D_level = 2

OPEN CUR_xx

FETCH NEXT FROM CUR_xx INTO @name,@level

WHILE @@FETCH_STATUS = 0

BEGIN

Print 'NAME : ' + @name + ' : LEVEL : ' + cast(@level as varchar(10))

select @id = max(id) + 1 from xx

INSERT INTO xx(name,id,D_level) VALUES('NewValue1',@id,'2')

FETCH NEXT FROM CUR_xx INTO @name,@level

END

CLOSE CUR_xx

DEALLOCATE CUR_xx

I just had to declare like this : Declare Cur_xx CURSOR STATIC FOR SELECT name,D_level FROM xx WHERE D_level = 2

Monday, September 3, 2012 - 9:56:55 AM - Jeremy Kadlec

SOM,

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?

Thank you,
Jeremy Kadlec

Monday, September 3, 2012 - 6:46:16 AM - SOM

I want to know what will happen for the below scenario.

I have done some testing and found that it is a problem.

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.

Thanks in advance

Monday, July 30, 2012 - 10:27:44 AM - Tony

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.

Thanks,

Tony

Friday, July 20, 2012 - 10:54:39 AM - Scott C

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 FOR
SELECT 
FROM dbo.table

FETCH NEXT FROM xyz
INTO

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.

It's a pretty simple process, although the description may look somewhat wordy. It sure beats typing 100+ variable declarations.

Monday, May 7, 2012 - 10:40:41 AM - Tony

Thank you both Tim and Jeremy. I really would like to use SELECT * in the cursor and avoid using variables just like my example in Oracle. But if I have to use variables then...

Thanks again for your time.

Tony

Monday, May 7, 2012 - 9:59:28 AM - Jeremy Kadlec

Deepak,

Did these two sections in this tip make sense to you:

Example SQL Server Cursor
SQL Server Cursor Components

Please let me know.

Thank you,
Jeremy Kadlec

Monday, May 7, 2012 - 9:57:52 AM - Jeremy Kadlec

Tony,

Did you see these two posts?

Monday, April 16, 2012 - 12:48:56 PM - Tim Cullen
Friday, April 20, 2012 - 11:53:22 AM - Jeremy Kadlec

Did they help point you in the right direction?

If the issue is creating variables for each column, I would try to use Tim's code as an example.

HTH.

Thank you,
Jeremy Kadlec

Tuesday, May 1, 2012 - 4:16:43 PM - Tony

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?

Below is the equivalent in ORALCE:

for emp_cur in (select * from employee order by empID) loop

str_empFormalName := emp_cur.empLastName || ', ' || emp_cur.empFirstName || ', ' || emp_cur.empMiddleInitial;

   update tempEmployee1
      set empFormalName = str_empFormalName;
    where empID = emp_cur.empID;

insert into tempEmployee2 (empID, empLastName, empFirstName, empMiddleInitial, empFormalName)
values (emp_cur.empID, emp_cur.empLastName, emp_cur.empFirstName, emp_cur.empMiddleInitial);

end loop;

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.

Thanks,

Tony

Tuesday, May 1, 2012 - 1:22:39 PM - Deepak

yes sir you are right..But i want to understand the concept of cursor with a simple and easy example...

please help me sir with better eaxample through which i can understand the concept easily...as i am the beginner in SQL Server...

Tuesday, May 1, 2012 - 9:07:01 AM - Armando Prato

WHY do you need a cursor, Deepak? What post processing do you need to do on the data that can't be done using a SET operation?

Cursors should never be your first choice for data access.

Monday, April 30, 2012 - 6:53:56 AM - Deepak

i want to use cursor in this table ..want to fetch value from id through cursor..please help me immediately..

Stu_Id	Stu_Name	Stu_Class
1	Komal	10
2	Ajay	10
3	Rakesh	10
4	Bhanu	10
5	Santosh	10
6	Tanuj	10

i want to use cursor in this table ..want to fetch value from id through cursor..please help me immediately..

Friday, April 20, 2012 - 2:20:20 PM - Jeremy Kadlec

Tony,

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,
Jeremy Kadlec

Friday, April 20, 2012 - 1:17:52 PM - Tony

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.

Thanks again for your help.

Tony

Friday, April 20, 2012 - 12:20:38 PM - Jeremy Kadlec

Tim,

Thank you for the quick response and alternatives.

Thank you,
Jeremy Kadlec

Friday, April 20, 2012 - 11:53:22 AM - Jeremy Kadlec

Tony,

I apologize for my delayed response. I think this might be the code you are looking for.

DECLARE @empID INT
DECLARE @empFirstName varchar(50)
DECLARE @empLastName varchar(50)

DECLARE emp_cur CURSOR FOR
SELECT empID, empFirstName, empLastName
FROM Employee
ORDER BY empID;

OPEN emp_cur
FETCH NEXT FROM emp_cur INTO @empID, @empFirstName, @empLastName

WHILE @@FETCH_STATUS = 0
BEGIN

-- PRINT empID, empFirstName, empLastName here

FETCH NEXT FROM emp_cur INTO @empID, @empFirstName, @empLastName

END
CLOSE emp_cur;
DEALLOCATE emp_cur;

Thank you,
Jeremy Kadlec

Monday, April 16, 2012 - 12:48:56 PM - Tim Cullen

Tony:

Here is an example of using a non-cursor loop to get the information:

SET NOCOUNT ON;

DECLARE @Transactions TABLE

(EntryID INT IDENTITY(1,1) PRIMARY KEY, TransactionTypeID INT, TransactionType VARCHAR(150))

DECLARE @Start INT = 1, @End INT, @TransactionTypeID INT, @TransactionType VARCHAR(150)

INSERT INTO @Transactions (TransactionTypeID, TransactionType)

SELECT TransactionTypeID, TransactionType FROM dimTransactionType WITH (NOLOCK)

SELECT @End = @@ROWCOUNT

WHILE @Start <= @End

BEGIN

SELECT @TransactionTypeID = TransactionTypeID, @TransactionType = TransactionType

FROM @Transactions WHERE EntryID = @Start

PRINT @TransactionTypeID

PRINT @TransactionType

SELECT @Start += 1

CONTINUE

END

SET NOCOUNT OFF;

Monday, April 16, 2012 - 12:40:15 PM - Tim Cullen	Back To Top (16943)
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?

Monday, April 16, 2012 - 11:06:31 AM - Tony

I have an EMPLOYEE table which consists of empID, empLastName, empFirstName, empAddress1... columns.

How do I reference a specific column in a cursor using SELECT * from EMPLOYEE?

Thank you very much.

Example code:

DECLARE emp_cur CURSOR FOR
SELECT * FROM Employee
ORDER BY empID;

OPEN empl_cur
FETCH NEXT FROM emp_cur
WHILE @@FETCH_STATUS = 0
   BEGIN
      -- PRINT empID, empFirstName, empLastName here
   END
CLOSE emp_cur;
DEALLOCATE emp_cur;

Wednesday, February 9, 2011 - 4:47:35 PM - Douglas Brum

Jeremy,

thanks for the tips. It helped a lot.

Cheers,

Douglas Brum

Wednesday, January 27, 2010 - 8:28:35 PM - admin

Eric,

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?

If not, this code needs to be amended.

If so, can you provide some sample data and the code?

Thank you,
The MSSQLTips Team

Wednesday, January 27, 2010 - 9:37:15 AM - emcquaid

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?

Cheers,

Eric

Thursday, October 9, 2008 - 5:36:31 PM - aprato	Back To Top (1948)
Ahhhh, yes. Good one, Tim. I can't tell you how many times I've seen SELECT * used when declaring a cursor.

Thursday, October 9, 2008 - 9:45:15 AM - timothyrcullen	Back To Top (1944)
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.

Tuesday, October 7, 2008 - 7:27:41 AM - aprato	Back To Top (1932)
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.

SQL Server Cursor Example

Problem

Solution

SQL Server Cursor Example

What is a SQL Server Cursor

How to Write a Cursor in SQL Server

Cursor in SQL Server

Explanation of Cursor Syntax in SQL Server

Why Use a Cursor in SQL Server

When to Use a SQL Server Cursor

SQL Server Cursor Types

SQL Server Cursor Alternatives

How to Avoid Cursor in SQL Server

Difference Between While Loop and Cursor in SQL Server

Additional SQL Server Cursor Examples

Next Steps

About the author

Comments For This Article

Related Articles