Sql-server – To use a cursor or something else in the while loop

cursorssql-server-2008-r2t-sql

From reading around the net it seems the use of cursors is frown upon but no one ever seemed to give an alternative to them. In my case I need to set row by row though the results of a select statement and pass a few columns from each row into another stored procedure for processing. The only way I see to do this is to create a cursor and run a while loop to use the cursor to grab each row so the data can be processed. So is using a cursor to step though each row from a select statement for processing the best way to do this or is there another way? In my case I have to make sure every single row is processed.

Best Answer

Ideally, you should change the other stored procedure to work on a set rather than take individual values (or create a new stored procedure that works on the set). If you show the actual logic in the procedure and the schema of the affected table(s), rather than just describe the high level process as a word problem, you can get some ideas about that. One way to avoid calling the procedure multiple times would be to pass the values for the different rows as a single set using a table-valued parameter (but again this requires re-writing the procedure or creating a new one).

In the meantime, if you absolutely must use a loop, you must also be willing to accept the performance consequences. One way to offset those - in cases where you really are using a cursor as a read-only, single pass through a set - is to use the following keywords when declaring your cursor:

DECLARE c CURSOR LOCAL FAST_FORWARD
FOR SELECT ...

More info here:

http://www.sqlperformance.com/2012/09/t-sql-queries/cursor-options

Related Solutions

Sql-server – Declaring cursor to loop over some values in SQL Server 2008 R2

The MSDN/BOL topic for DECLARE CURSOR contains example syntax for cursor usage. That said, as I had 5 minutes to spare on the train this morning...

-- Create temporary table for Business Units (replace with the "real" table)
DECLARE @BusinessUnit TABLE (BU NVARCHAR(MAX))

INSERT @BusinessUnit
SELECT 'India' UNION SELECT 'International' UNION SELECT 'US' UNION SELECT 'UK'

DECLARE @K TABLE (BU NVARCHAR(MAX),K NVARCHAR(MAX),Y MONEY,A MONEY,D MONEY,YP MONEY)
DECLARE @FY INT
DECLARE @BU NVARCHAR(MAX)

SET @FY = 2012

DECLARE BU_cursor CURSOR FAST_FORWARD FOR
SELECT BU FROM @BusinessUnit

OPEN BU_cursor
FETCH NEXT FROM BU_cursor INTO @BU

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT @K (BU, K, Y, A, D, YP)
    EXEC dbo.SP_Report @FY, @BU

    FETCH NEXT FROM BU_cursor INTO @BU
END

CLOSE BU_cursor
DEALLOCATE BU_cursor

SELECT * FROM @K

Sql-server – How to reduce the run time of stored procedure by getting rid of cursors

You want to increase the speed of your cursors? Wrap them in a transaction. If you are processing millions of records and don't want/need them all in one transaction, you can commit it on occasion to reduce resources.

I did this with a cursor that took an hour to run (this is an extreme case) and afterword it ran in 1 1/2 minutes.

I know that does not answer you question but it might help you avoid doing the conversion until your more familiar with SQL. The answer to your question is... experience. There is no magic site or book you can read, it just takes time learning to do more and more in a single statement.

Best Answer

Related Solutions

Sql-server – Declaring cursor to loop over some values in SQL Server 2008 R2

Sql-server – How to reduce the run time of stored procedure by getting rid of cursors

Related Question