All programming languages have it's own way of handling iteration operations. In SQL server one way to do this is using a 'CURSOR'.
Although there are some other sql iterative solutions like while loop, here we focus only on T-sql cursors.
When writing sql queries we often need to loop through different types of records sets, in order to achieve certain tasks. It's very common.
Personally I always forget cursor syntax & when I need to use it what I'm doing is, just get it copied from somewhere else. :D Let's look into cursors in detail.
How it operates
Cursor is a database object & it manipulates given record set row by row basis. Which means it take first record, perform what it needs to be done & move to next record and do whatever the thing mentioned. It goes like this until the end of the record set.
In this example I'm trying to read some data from few tables & insert them into a temp table called #TEMP_TABLE. First of all go to following link to download complete Sql query for creating required tables (Test data included). I've used MSSQL Server 2012.
(3 KB)
After download, open this file using SQL Server Management Studio & execute it. Then you have test data to see how cursor works ! Following is a code snippet of a typical sql cursor example.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | USE TestDB_CURSOR CREATE TABLE #TEMP_TABLE(CUSTOMER VARCHAR(50), ITEM VARCHAR(50)) DECLARE @Cusname varchar(50) DECLARE @Item varchar(50) DECLARE TEST_CURSOR CURSOR FOR SELECT CUS.CusName, ITEM.ItemName FROM tblOrder AS ORD INNER JOIN tblCustomer AS CUS ON ORD.CustomerID = CUS.CustomerID INNER JOIN tblItem AS ITEM ON ORD.ItemID = ITEM.ItemID OPEN TEST_CURSOR FETCH NEXT FROM TEST_CURSOR INTO @Cusname, @Item WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO #TEMP_TABLE VALUES(@Cusname, @Item) FETCH NEXT FROM TEST_CURSOR INTO @Cusname, @Item END CLOSE TEST_CURSOR DEALLOCATE TEST_CURSOR SELECT * FROM #TEMP_TABLE DROP TABLE #TEMP_TABLE |
Line no. 5,6 => Declaration of cursor variables.
Line no. 8 => Declaration of cursor [Test_Cursor is the name of the cursor]. There are more cursor options like FORWARD_ONLY, FAST_FORWARD etc... But it's not discuss in here. You can view more details form MSDN
Next, a select statement to retrieve values. Here we are selecting two values. This is the result set we are going to loop.
Then start the cursor
In line no 15 => assign values which are got from select statement into the cursor variables.
Important : Selected columns types and relevant variables types must match each other.
Next thing is While loop. @@FETCH_STATUS = 0 means fetching was successful. If something goes wrong and fetching did not happened correctly it will get a value other than 0 (Usually this will be -1 or -2). If @@FETCH_STATUS = 0 iteration will continue and will stop when it become -1 or -2.
Line no. 18 => Values assigned to cursor variables insert into temporary table created above. This is optional & should change according to your scenario.
Then move onto next raw of the result set and assign cursor variables with relevant values. This iterative process will continue until end of the result set row count.
When everything happened correctly we need to close the cursor & release the resources use by cursor itself. That's it for cursor & you may execute the sql query to see the final output.
Performance Concerns
In generally it is considered as, looping in T-SQL is much slower than the looping structures of other programming languages' such as .Net, VB6 or Java etc.. But it may vary depend on the scenario that someone is working. It's totally up to you to decide which one to use :)
This post regarding sql cursors is the very first technical post written by me :) Hope you liked it. Got something to say?? Please, do comment below...!
See you soon with a another post.!!!
Post a Comment