April 14, 2021 at 4:07 pm
/*Declare a table variable that you will iterate using the seeded integer value iRow */
DECLARE @tLoopTable table(iRow int IDENTITY (1, 1) NOT NULL, TABLE_SCHEMA nvarchar(128), TABLE_NAME nvarchar(128) UNIQUE (iRow))
I've got no problem with the general recommendation. But I strongly prefer a temp table to a table variable. Also, you should always key the table on the IDENTITY value. Otherwise you're doing a full scan of the table for every SELECT from it!
CREATE TABLE #work ( id int IDENTITY(1, 1) PRIMARY KEY, ...other_columns... )
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 14, 2021 at 4:38 pm
I don't like the dual FETCH statements most people use for cursors. It often causes errors when one cursor gets changed and the other doesn't. I'd like your feedback on the alternative style for cursors below.
DECLARE @fetch2_limit int = 3; /*optional, of course*/
DECLARE @state_abbreviation char(2);
DECLARE @state_name varchar(30);
DECLARE cursor_states CURSOR LOCAL FAST_FORWARD FOR
SELECT state_name, state_abbreviation
FROM dbo.states
ORDER BY state_name;
OPEN cursor_states;
WHILE 1 = 1
BEGIN
FETCH NEXT FROM cursor_states INTO @state_name, @state_abbreviation
IF @@FETCH_STATUS <> 0
IF @@FETCH_STATUS = -2 /* row missing, but NOT yet at end of data */
BEGIN
/* if one row is missing (likely been deleted), we still need to process all the remaining rows */
SET @fetch2_limit = @fetch2_limit - 1
IF @fetch2_limit <= 0
/* however, we also want to make sure we don't get into some kind of endless loop if something odd is going on */
BREAK
ELSE
CONTINUE
END /*IF*/
ELSE
BREAK;
/* do some processing */
IF 'x' <> 'y' /*some complex condition(s)*/
/* we've decided this row is done being processed, skip to the next row */
CONTINUE
/* extended processing here */
END /*WHILE*/
CLOSE cursor_states;
DEALLOCATE cursor_states;
I know the downsides to this (the generic "1 = 1"), but I've added an example of one of the advantages I think this method adds to compensate.
Often cursors are used for complex logic (else it would be done in a set-based manner). In those cases, it's often better to be able to skip a row after some preliminary processing. That's a pain with the dual-FETCH method. You have to embed a bunch of IFs (so that you can fall thru to the FETCH at the end) or repeat the FETCH yet again (which I've seen done, ugh!) before looping back yourself.
With the FETCH at the start, you can simply CONTINUE to go to the next row.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 14, 2021 at 4:47 pm
As for when to use a cursor, I've used these:
For looping thru dbs, the classic sp_foreachdb (my souped-up version, I don't use the MS version).
For relatively complex processing where you need a lot of control, esp. over the format of the output. For example, I have a dbo.sp_listcolumns proc that will list the columns from a table with a huge number of controllable options: the max columns to have on a line; what the format of the output will be (such as to DECLARE @column_name for all columns in a table, MAX(LEN( for every column, etc.; to always list identity first; and so on. I suppose one could write a set-based SELECT that did all that, but it would make the code very hard to write and to change later.
Cursors are also often useful for handling errors when there can be multiple errors. You load the errors into a table, then cursor thru them at the end. Since it's rare to have errors (normally), this is easy, convenient and not too much overhead.
********************************************************************************************************
That said, use of cursors should still be limited. I have a proc, dbo.sp_helpindex$, that replaces the standard sys.sp_helpindex (not enough info now), which I can pass a list of index names/patterns to list info for. It provides a lot of output, but I still use a set-based approach for it, because I don't need very close control over the format of the output.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 11, 2024 at 5:55 pm
I STRONGLY second the call by @acloughnane to move the best practices paragraph to the top of the article. While this article is very informative, cursors should not be used in a production environment unless there is zero alternative....and in SQL there is very rarely zero alternative. I've been working with databases for over 25 years and I can count on 1 finger the number of times I put something into production that uses a cursor. And I regretted it!
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply