Cursor - High-Flying SQL Tips


SQL Tips

Add a Unique Constraint to a Column

CURSOR

DELETE

Dirty Reads and Phantom Reads

INSERT

Recursive SQL

ROW_NUMBER() - Automatic Paging

SELECT, FROM

SQL Default Date Format

UPDATE

WHERE

Sponsored Links

73058_New Scooba® 230 Floor Washing Robot + Free Shipping!

 

Cursor

Here we look at the Cursor command in SQL.

Let's say you run a Select query and the results returned included more than one row of data.  You then need to perform an action with each one of the rows on an individual basis as part of your SQL query.  The SQL Cursor object allows us to do this.  It will let you go through each individual row, run some SQL, and then move onto the next row for you to perform some more SQL, or not.

For an example, say you ran a Select query which returned a list of products.


-- Select Product IDs where the value in SearchWord
-- exists in the field Keyword_Tags
SELECT Product_ID FROM Product_Keyword_Tags WHERE Keyword_Tags LIKE '%' + @SearchWord + '%'


You then needed to go through each one of the rows in the returned results set and perform an action on each row at a time, such as inserting the data into another table.  You could achieve this in more than one way such as creating a temporary table and adding an ID column and then looping through the temporary table row by row to perform your actions.  Using the SQL Cursor allows us to do this without having to create a temporary table.

The following shows how to iterate through each of the rows of products in the returned dataset from the above SQL query using a Cursor.


-- Select Product IDs where the value in SearchWord
-- exists in the field Keyword_Tags
-- then Insert the ID into a temporary table
DECLARE @TagID int DECLARE Tag_Cursor CURSOR FOR SELECT Product_ID FROM Product_Keyword_Tags WHERE Keyword_Tags LIKE '%' + @SearchWord + '%'
OPEN Tag_Cursor
FETCH NEXT FROM Tag_Cursor INTO @TagID
WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO #SearchResults (Key_Tag_ID) VALUES (@TagID)
FETCH NEXT FROM Tag_Cursor INTO @TagID

END

CLOSE Tag_Cursor
DEALLOCATE Tag_Cursor


So we use the DECLARE keyword to create our cursor and provide a SQL query for the cursor, the SELECT query from which the cursor will iterate through each of the rows in the returned dataset.  We then need to OPEN the cursor to begin using it.  The FETCH NEXT FROM command gets the next row from the dataset of the cursor's SELECT query, and in this case we're only returning one column in the returned dataset, and we set the value of the variable @ to the Product_ID value in that row.  Using a WHILE loop we loop through the Cursor's dataset until FETCH_STATUS no longer equals 0 which means we've read through all the rows.  Finally, we CLOSE and DEALLOCATE the cursor to make sure the resources and locks are freed up.

The SQL Cursor uses up resources and there are maybe better ways to achieve what you're doing and I'd encourage you to use an alternative rather than using it in production code , but it does provide a simple syntax for achieving what it does, useful for performing one off queries.