CURSOR


InterServer Web Hosting and VPS
Sony Playstation PS4 Pro Custom Build NVMe SSD Drive - Enclosure case and Crucial NVMe SSD
PS4 Pro Custom External SSD NVMe Drive

How I built a PS4 Pro Custom External SSD NVMe Drive...

NUC8i7BEH with memory
Intel NUC8I7BEH Review and Custom Build

This is my review of the Intel NUC8i7BEH and a summary of my build...

Card image cap
Asus M5A88-M EVO Review

This is my review of the Asus M5A88-M EVO motherboard...

Corsair Vengeance Blue 8GB (2x4GB) DDR3 PC3-12800C9 1600MHz Dual Channel Kit
Corsair Vengeance Blue Review

This is my review of the Corsair Vengeance Blue 8GB (2x4GB) DDR3 PC3-12800C9 1600MHz Dual Channel Kit memory...

Robotic Arm with USB PC Interface Review
Robotic Arm with USB PC Interface Review

This is my review of the Robotic Arm with USB PC Interface...

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.


Sponsored Links
SolarSystem - AngularJS Demo
SolarSystem - Angular JS Demo

Check out my first Angular JS demo hosted in Azure, it helped win my first Angular contract. Put the sound on.