In this post, describing how to loop using SQL Server cursor fetch fast_forward, I’m going to give an example of looping through a temporary table using a cursor and call a stored procedure for each iteration. The focus of this post is on the fast_forward cursor which is a shorthand way of saying the cursor is forward only and read only. The fast_forward cursor type has performance optimizations enabled so this allows the speed to be about as good as possible. The SQL Server query processing team wrote up a very technical and detailed article on how fast_forward cursors work and it can be found here. It may come as a surprise that there are cursors that allow other actions as well. I’ll be covering those in future posts. On to the example…
The majority of code below is just wiring up code to run the example. I create a stored procedure and temporary table which will be used by the cursor. The cursor specific code is found on lines 55-83.
-- CURSOR EXAMPLES DATABASE PREPARATIONS -- you should change this to whatever database you'd like to run -- this example in. I'm using AdventureWorks2008 since many folks -- may already have it. USE AdventureWorks2008; -- SAFEGUARD - check if the stored procedure exists and if so delete it IF OBJECT_ID('[uspGiveRecommendationBasedOnFlag]') IS NOT NULL DROP PROCEDURE [uspGiveRecommendationBasedOnFlag]; GO -- create the demo stored proc. we'll delete it when we're done. CREATE PROCEDURE [uspGiveRecommendationBasedOnFlag] @articleFlag BIT, @articleName NVARCHAR(50) AS BEGIN -- you would do some actual work here in a real app IF(@articleFlag = 1) PRINT 'You should publish the article: ' + @articleName; ELSE PRINT 'You shouldn''t publish the article: ' + @articleName; END GO -- SAFEGUARD -- not familiar with the .. syntax below? check out this blog post IF OBJECT_ID('tempdb..#cursorExample') IS NOT NULL BEGIN DROP TABLE #cursorExample END -- create the temp table CREATE TABLE #cursorExample ( id INT IDENTITY(1,1) NOT NULL, flag BIT NOT NULL, article_name NVARCHAR(100) ) -- fill the temp table with sample data INSERT INTO #cursorExample (flag, article_name) VALUES (1,'cooking'),(1,'crafts'),(0,'flying'), (1,'T-SQL'), (0,'wood carving'),(1,'gardening'), (0,'reading'),(1,'beer brewing'),(0,'gaming'); -- create the variables we'll use when inside the the loop DECLARE @articleFlag BIT; DECLARE @articleName NVARCHAR(100); ------------------ CURSOR SPECIFIC CODE BEGIN -------------------- -- declare the fast_forward cursor. notice we are essentially -- using a SELECT query to build a list to iterate through DECLARE myCursorSample CURSOR fast_forward FOR SELECT flag, article_name FROM #cursorExample ; -- now we have to open the cursor and tell it to go -- acquire (i.e. FETCH) the NEXT (i.e. first since this is the first call) -- item in the list OPEN myCursorSample FETCH NEXT FROM myCursorSample INTO @articleFlag, @articleName ; WHILE @@FETCH_STATUS = 0 BEGIN -- DEBUG -- PRINT ('Before stored proc execution cursor value: ' + @articleName ) ; EXEC [uspGiveRecommendationBasedOnFlag] @articleFlag = @articleFlag, @articleName = @articleName; -- get the next article from the table using the cursor FETCH NEXT FROM myCursorSample INTO @articleFlag, @articleName; END CLOSE myCursorSample ; DEALLOCATE myCursorSample; ------------------ CURSOR SPECIFIC CODE END -------------------- -- CLEAN UP EXAMPLE DROP PROCEDURE [uspGiveRecommendationBasedOnFlag];
I tried to make the code as clear a possible with comments but I should point out a few things. The SAFEGUARD comments are there only to show where I’m doing existence checks before actually performing an action on the database. This is good practice for things like change scripts where running the same code twice shouldn’t cause errors or break something. Second, this code is very well documented. Although this amount of commenting is overboard for most production systems you should try to document why you’re using the cursor and perhaps your justification for doing so. This should help minimize improper use of cursors in the codebase. Lastly, while this example stored proc call isn’t doing anything a set based query couldn’t, you can imagine cases where based on values in a table you might have to carry out significant business logic actions which are not conducive to set based operations.
The results of the code execution are below.
(9 row(s) affected) You should publish the article: cooking You should publish the article: crafts You shouldn't publish the article: flying You should publish the article: T-SQL You shouldn't publish the article: wood carving You should publish the article: gardening You shouldn't publish the article: reading You should publish the article: beer brewing You shouldn't publish the article: gaming
Valid for SQL Server 2005, 2008, R2, 2012, 2014.