Learn more about SQL Server tools

   
   















































SQL Server Cursor Example

MSSQLTips author Jeremy Kadlec By:   |   Read Comments (52)   |   Related Tips: 1 | 2 | 3 | More > DBA Best Practices
Problem

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

Solution

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

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

Example SQL Server Cursor

Here is an example cursor from 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

SQL Server Cursor Components

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_name> 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
    • 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

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:

SQL Server Cursor Analysis

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

  • Online Transaction Processing (OLTP) - In most OLTP environments, SET based logic makes the most sense for short transactions.  Our team has run into a third party application that uses cursors for all of its processing, which has caused issues, but this has been a rare occurrence.  Typically, SET based logic is more than feasible and cursors are rarely needed.
  • Reporting - Based on the design of the reports and the underlying design, cursors are typically not needed.  However, our team has run into reporting requirements where referential integrity does not exist on the underlying database and it is necessary to use a cursor to correctly calculate the reporting values.  We have had the same experience when needing to aggregate data for downstream processes, a cursor based approach was quick to develop and performed in an acceptable manner to meet the need.
  • Serialized processing - If you have a need to complete a process in serialized manner, cursors are a viable option.
  • Administrative tasks - Many administrative tasks need to be executed in a serial manner, which fits nicely into cursor based logic, but other system based objects exist to fulfill the need.  In some of those circumstances, cursors are used to complete the process.
  • Large data sets - With large data sets you could run into any one or more of the following:
    • Cursor based logic may not scale to meet the processing needs.
    • With large set based operations on servers with a minimal amount of memory, the data may be paged or monopolize the SQL Server which is time consuming can cause contention and memory issues.  As such, a cursor based approach may meet the need.
    • Some tools inherently cache the data to a file under the covers, so processing the data in memory may or may not actually be the case.
    • If the data can be processed in a staging SQL Server database the impacts to the production environment are only when the final data is processed.  All of the resources on the staging server can be used for the ETL processes then the final data can be imported.
    • SSIS supports batching sets of data which may resolve the overall need to break-up a large data set into more manageable sizes and perform better than a row by row approach with a cursor.
    • Depending on how the cursor or SSIS logic is coded, it may be possible to restart at the point of failure based on a checkpoint or marking each row with the cursor.  However, with a set based approach that may not be the case until an entire set of data is completed.  As such, troubleshooting the row with the problem may be more difficult.

SQL Server Cursor Alternatives

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

Next Steps
  • When you are faced with a data processing decision determine where you stand with SQL Server cursor usage.  They may or may not have a place in your application or operational processes.  There are many ways to complete a task, so using a cursor could be a reasonable alternative or not.  You be the judge.
  • If you run into issues with another coding technique and need to get something done quickly, using a cursor may be a viable alternative.  It may take longer to process the data, but the coding time might be much less.  If you have a one time process or nightly processing, this could do the trick.
  • If cursors are shunned in your environment, be sure to select another viable alternative.  Just be sure the process will not cause other issues.  As an example, if a cursor is used and millions of rows are processed will this potentially flush all of the data from cache and cause further contention?  Or with a large data set will the data be paged to disk or written to a temporary directory?
  • As you evaluate a cursor based approach versus other alternatives make a fair comparison of the techniques in terms of time, contention and resources needed.  Hopefully these factors will drive you to the proper technique.


Last Update: 1/23/2014


About the author
MSSQLTips author Jeremy Kadlec
Jeremy Kadlec is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com, Baltimore SSUG co-leader and SQL Server MVP since 2009.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



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

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 Read The Tip

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 Read The Tip

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 Read The Tip

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 08, 2014 - 5:38:05 PM - sql1 Read The Tip

Thanks for the article.. it was helpful!!

 


Tuesday, July 01, 2014 - 2:56:20 AM - amit Read The Tip

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 Read The Tip

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 Read The Tip

Thank you very much!


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

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 Read The Tip

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 Read The Tip

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 Read The Tip

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 09, 2013 - 1:25:31 PM - Nina Read The Tip

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 Read The Tip

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 08, 2013 - 6:11:42 AM - Jeremy Kadlec Read The Tip

@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 03, 2013 - 4:44:48 PM - Wayne Read The Tip

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 09, 2013 - 1:41:59 PM - Mark Kadlec Read The Tip

Good tips.....and you have a great name!


Wednesday, March 06, 2013 - 10:23:25 AM - tani Read The Tip

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 02, 2012 - 8:53:31 AM - Grignar Grenac Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

 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 Read The Tip

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 Read The Tip

 

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 Read The Tip

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 Read The Tip

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 04, 2012 - 8:24:07 AM - Jeremy Kadlec Read The Tip

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 04, 2012 - 2:49:42 AM - SOM Read The Tip

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 03, 2012 - 9:56:55 AM - Jeremy Kadlec Read The Tip

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 03, 2012 - 6:46:16 AM - SOM Read The Tip

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 Read The Tip

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 Read The Tip

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 07, 2012 - 10:40:41 AM - Tony Read The Tip

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 07, 2012 - 9:59:28 AM - Jeremy Kadlec Read The Tip

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 07, 2012 - 9:57:52 AM - Jeremy Kadlec Read The Tip

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 01, 2012 - 4:16:43 PM - Tony Read The Tip

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 01, 2012 - 1:22:39 PM - Deepak Read The Tip

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 01, 2012 - 9:07:01 AM - Armando Prato Read The Tip

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 Read The Tip

 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 Read The Tip

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 Read The Tip

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 Read The Tip

Tim,

Thank you for the quick response and alternatives.

Thank you,
Jeremy Kadlec


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

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 Read The Tip

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 Read The Tip

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 Read The Tip

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 09, 2011 - 4:47:35 PM - Douglas Brum Read The Tip

Jeremy,

 

thanks for the tips. It helped a lot.

 

Cheers,

Douglas Brum


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

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 Read The Tip

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 09, 2008 - 5:36:31 PM - aprato Read The Tip

 Ahhhh, yes. Good one, Tim.  I can't tell you how many times I've seen SELECT * used when declaring a cursor.


Thursday, October 09, 2008 - 9:45:15 AM - timothyrcullen Read The Tip

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 07, 2008 - 7:27:41 AM - aprato Read The Tip

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.




 
Sponsor Information