SQL Server Cursor Example

By:   |   Updated: 2024-05-02   |   Comments (78)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | > TSQL


Problem

In my T-SQL code, I always use set-based operations. I have been told these types of SQL queries are what the SQL Server relational database engine is designed to process and 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. Each of these camps, have different reasons for their stand on cursor usage in the DBMS. Regardless, they probably have a place in particular circumstances and not others. It boils down to your understanding of the coding technique, then your understanding of the problem at hand to decide if cursor-based processing is appropriate. 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 and then answer all pertinent questions in this SQL tutorial.

SQL Cursor Example

Here is a SQL Server cursor example 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 that loops over a predetermined number of rows one at a time. The purpose of 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. There are many options and types of cursors, such as:

  • Read_Only
  • Fast_Forward
  • Forward_Only
  • Scroll
  • Static Cursors
  • Dynamic
  • Local
  • Global
  • Keyset
  • Scroll_Locks
  • Optimistic

How to Write a Cursor in SQL Server with Transact-SQL

Creating a SQL Server cursor with T-SQL is a consistent method that can process data on a set of rows. Once you learn the steps, you can easily duplicate them with various sets of logic to loop through data. Let's walk through the steps:

  1. Declare the variables (file names, database names, account numbers, etc.) needed in the logic and initialize the variables. Specify the variable name and data type.
    • This logic would be updated based on your needs.
  2. Declare the 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 SQL statement) to populate the records. The cursor rows can be defined using user-defined or temporary tables, including filtering with a WHERE clause and ordering data with an ORDER BY clause. The cursor name can be anything meaningful. This is immediately followed by opening the cursor.
    • This logic would be updated based on your needs.
  3. Fetch a record from the cursor to begin the data processing.
    • NOTE - There are an equal number of variables declared for the cursor, columns in the SELECT SQL 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.
  4. 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 and is performed on each row.
    • This logic would be updated based on your needs.
  5. Fetch the next record from the cursor as you did in step 3 and then step 4 is repeated by processing the selected data.
  6. Once all the data has been processed, you close the cursor.
  7. As a final and important step, you need to deallocate the cursor to release all the internal resources SQL Server is holding.

From here, check out the examples below to understand 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 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 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 cursor
CLOSE db_cursor  

-- 7 - Deallocate 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 one or many variables and associated columns.
  • OPEN statement - Open the cursor to begin data processing.
  • FETCH 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 any DML or administrative logic.
  • CLOSE statement - Releases the current data and associated locks, but permits the cursor to be re-opened.
  • DEALLOCATE cursor 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 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 seen 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 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 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 and 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 only occur when the final data is processed. All 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 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 Cursors in SQL Server

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

Difference Between While Loop and Cursor in SQL Server

Daniel Farina wrote 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. 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.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Jeremy Kadlec 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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2024-05-02

Comments For This Article




Wednesday, July 10, 2024 - 12:04:45 PM - Ronald Duncan Back To Top (92379)
I avoid cursors where possible. E.g. When tidying up input data, I will probably use AWK or if it is in Excel a VBA script.

However sometimes the mess is so bad that it needs brought into a database and manipulated with a cursor.

In which case I will probably be fetching multiple columns and doing some debugging.

The common pattern with Cursors is a double fetch. Fetch first record and then while loop and then fetch next record.
A nice alternative is to use an UNTIL loop which sadly does not exist in SQL server, however it is easily implemented with a GOTO
startLabel:
... fetching
IF fetched
...body of loop
GOTO startLabel -- end of UNTIL loop

This breaks all the rules do not use cursors, do not use GOTO, but it gets rid of the common error if you have code with two fetches where only one gets updated.

Thursday, June 29, 2023 - 9:51:37 AM - Jeremy Kadlec Back To Top (91354)
Seid and Brock,

Thank you for the two most recent comments. That makes my day!

Thank you,
Jeremy Kadlec
Community Co-Leader

Thursday, June 29, 2023 - 4:46:43 AM - seid mohammed Back To Top (91353)
this is the best website for database education i will be here forever.thank you.

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 Back To Top (89065)
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 Back To Top (88666)
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 Back To Top (85483)

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 Back To Top (85465)

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 Back To Top (78642)

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 Back To Top (77819)

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 Back To Top (77815)

Joe,

Thank you for the feeback and history.

Thank you,
Jeremy Kadlec


Tuesday, October 2, 2018 - 2:15:25 PM - Joe Celko Back To Top (77786)

 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 Back To Top (75590)

 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 Back To Top (75363)

 

 Thanks for the post. It was very helpful.


Friday, July 8, 2016 - 9:09:15 AM - Bryan Back To Top (41837)

 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 Back To Top (37337)

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 Back To Top (35885)

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 Back To Top (35418)

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 Back To Top (35243)

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 Back To Top (35121)

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 Back To Top (34444)

Thanks for the article.. it was helpful!!

 


Tuesday, July 1, 2014 - 2:56:20 AM - amit Back To Top (32484)

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 Back To Top (30097)

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 Back To Top (29296)

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 Back To Top (29288)

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

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

1    msg          xxx

2    msg          xxx

1    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

1    msg          xxx

2    msg          xxx

1    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 Back To Top (28102)

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 Back To Top (28095)

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 Back To Top (27094)

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

 

 

ON

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 Back To Top (25370)

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 Back To Top (25351)

@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 Back To Top (25264)

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 Back To Top (22592)

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 Back To Top (20189)

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 Back To Top (19861)

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 Back To Top (19653)

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 Back To Top (19591)

 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 Back To Top (19572)

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 Back To Top (19536)

 

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 Back To Top (19530)

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 Back To Top (19516)

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 Back To Top (19386)

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 Back To Top (19380)

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 Back To Top (19376)

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 Back To Top (19374)

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 Back To Top (18854)

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 Back To Top (18709)

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 "\t@".
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 Back To Top (17302)

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 Back To Top (17299)

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 Back To Top (17297)

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 Back To Top (17225)

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 Back To Top (17222)

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 Back To Top (17218)

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 Back To Top (17197)

 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 Back To Top (17024)

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 Back To Top (17022)

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 Back To Top (17021)

Tim,

Thank you for the quick response and alternatives.

Thank you,
Jeremy Kadlec


Friday, April 20, 2012 - 11:53:22 AM - Jeremy Kadlec Back To Top (17019)

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 Back To Top (16945)

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 Back To Top (16940)

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 Back To Top (12895)

Jeremy,

 

thanks for the tips. It helped a lot.

 

Cheers,

Douglas Brum


Wednesday, January 27, 2010 - 8:28:35 PM - admin Back To Top (4803)

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 Back To Top (4798)

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.















get free sql tips
agree to terms