1. About SQL Cursor
Let us say in a SQL Script, in Stored Procedure, or in a function, we want to process the data record-by-record. In this case, we can go for a SQL Cursor. Note, using Cursor is not advisable as it eats up memory, network bandwidth and performs lock while processing the records. So, before writing a cursor think twice and use it when no other options are available. With this overhead, we will get full control over the record processing. In this example, we will create a SQL cursor to fetch record from the Jobs table of the pubs database and list the records. In real world, after reading the record, there will be business logic that performs various other actions based on the fetched data.
2. Variable to Hold Cursor Fetch Results
First, we must declare variables to hold the data read from the records. The data type of the variables should match with the data type of the record columns. Now look at the below code:
Explanation
- The
NoCountvariable is turned ON. This means when a query is executed affected row counts are not emitted as a message. - Here, we declare three variables for the SQL Script execution. Each variable will be mapped to a column in the
Jobstable of thePubsdatabase. We will see that soon.
3. Open SQL Cursor & Do Initial Fetch
An SQL Cursor works on four sequences of operation namely declare, open, fetch and close. The below code opens a cursor and performs a single fetch:
Explanation
- We named the SQL Cursor as JobCursor which we marked as
Forward_OnlyandRead_Only. The option Read_Only implies that we cannot change the data. The option Forward_Only states that cursor pointer moves only in forward directions. This mean, once the record is read, it cannot be read again with the same cursor wihtout reopening it. - The select statement here decides the record set which the cursor will read one by one for processing.
- Cursor’s
Openstatement is followed by the Cursor name. It opens the cursor for fetching the records one by one. Fetch Next Fromwill fetch the first record from the RecordSet. The into clause tells where to keep the record set columns. In our case, we opened the cursor to read three columns from the Jobs table. So, we provide three mapped variables in theInto Clause. After executing this statement, all these three variables hold the relevant data from the first record. When query returns zero records, fetch statement does not populate anything into these variables.
4. Iterate Through SQL Cursor
In the previous section we did our initial fetch. But, when the cursor returns more than one row, we need to iterate them one-by-one. Now look at the below code:
Explanation
- The
@@Fetch_Statusis a system function which will hold a value of Zero when the fetch was successful. A negative number indicates fetch failed because of various reason. Here, we form a while loop which quits when the fetch fails. Note, the cursor pointer advances in eachFetch Nextstatement and after some point in time, it goes beyond the last record @@Fetch_Status holds a value of -1. - Once we are inside the loop, we can make use the variables to form the business logic. In our case, we simply printed the fetched data.
- After processing a record, we make a call to Fetch Next to point the cursor to the next row. If there is no next row, we come out of the loop as @@FETCH_STATUS will not be 0.
5. Cursor Cleanup
After doing all the required process, we can close the Cursor and release the consumed resources. Have a look at the code below:
Explanation
- Using
Closestatement, we can close the cursor. But, later, we can reopen it as the cursor definition is retained. - The
Deallocatestatement releases all the resources associated to the Cursor. If we do not want to use the cursor again in the running context (Script), we can deallocate it.
6. Code Reference
|
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 27 28 29 30 31 32 33 |
-- Sample 01: Stop showing record count in the output SET NOCOUNT ON; -- Sample 02: Declare Variables Declare @JobId smallint; Declare @JobDesc varchar(50); Declare @MaxJobLvl tinyint; -- Sample 03: Declare & Open the Cursor Declare JobCursor Cursor Forward_Only Read_only For Select Job_id, Job_Desc, max_lvl from Jobs; OPEN JobCursor; -- Sample 04: Fetch the Data into Cursor FETCH NEXT FROM JobCursor INTO @JobId, @JobDesc, @MaxJobLvl; -- Sample 05: Iterate through the Records & Print it WHILE @@FETCH_STATUS = 0 BEGIN Print 'Job Id' + str(@JobId); Print 'Description' + @JobDesc; Print 'Maximum Job Level' + str(@MaxJobLvl); Print '===================================='; FETCH NEXT FROM JobCursor INTO @JobId, @JobDesc, @MaxJobLvl; END; -- Sample 06: Close the Cursor CLOSE JobCursor; -- Sample 07: Cleanup Cursor Resources DEALLOCATE JobCursor; |
Categories: SQL Server
1 reply ›
Do you like this Example? Please comment about it for others!!
This site uses Akismet to reduce spam. Learn how your comment data is processed.
Excellent work! Thanks for clarifying cursors