Today I decided to go back to the basics and show and explain to you a basic SQL Server cursor. In this example we will loop through the results of a table and select the description of each ID we find.
But first; one should understand the purpose of a cursor. As we all know. SQL Server is a relational database management system (RDBMS), and SQL is a transactional programming language. This means that it is designed to executre its work in all-or-nothing batches. The data base engine is optimised to work in the manner at in general it is more than sufficient to execute simple bulk or batch type transactions.
Therefore, as a general rule of thumb, and for good performance do not use cursors.
However, there are some situations in which cursors can be lifesavers. I can think of a few right off the bat:
1. Concurrent queries : Sometimes, in OLTP systems, there are just too many users actively querying a specific table. In order to be able to update the entire table without creating a huge lock that blocks all activity a cursor or a while loop can be helpful. (One should first look into for this though)
2.To create a for each type logic in T-SQL scripts. For example, on could create a script to copy each table and execute it with sp_executesql.
Now lets get to it then. Take this simple table as an example:
CREATE TABLE #ITEMS (ITEM_ID uniqueidentifier NOT NULL, ITEM_DESCRIPTION VARCHAR(250) NOT NULL) INSERT INTO #ITEMS VALUES (NEWID(), 'This is a wonderful car'), (NEWID(), 'This is a fast bike'), (NEWID(), 'This is a expensive aeroplane'), (NEWID(), 'This is a cheap bicycle'), (NEWID(), 'This is a dream holiday')
Now say we want to cut the selection of each description into 5 separate transactions. Here is the cursor syntax to do that. Enjoy
DECLARE @ITEM_ID uniqueidentifier -- Here we create a variable that will contain the ID of each row. DECLARE ITEM_CURSOR CURSOR -- Here we prepare the cursor and give the select statement to iterate through FOR SELECT ITEM_ID FROM #ITEMS OPEN ITEM_CURSOR -- This charges the results to memory FETCH NEXT FROM ITEM_CURSOR INTO @ITEM_ID -- We fetch the first result WHILE @@FETCH_STATUS = 0 --If the fetch went well then we go for it BEGIN SELECT ITEM_DESCRIPTION -- Our select statement (here you can do whatever work you wish) FROM #ITEMS WHERE ITEM_ID = @ITEM_ID -- In regards to our latest fetched ID FETCH NEXT FROM ITEM_CURSOR INTO @ITEM_ID -- Once the work is done we fetch the next result END -- We arrive here when @@FETCH_STATUS shows there are no more results to treat CLOSE ITEM_CURSOR DEALLOCATE ITEM_CURSOR -- CLOSE and DEALLOCATE remove the data from memory and clean up the process