Cursor in t-sql is very useful feature. In SQL, it is difficult to set a pointer to a set of data and evaluate other tables or data while in the loop. Simply, cursors are temporary storage to a row data or some column data in a row you set in a table so that they can be used to evaluate other data while going through each row in the table. Because cursors use server resources, you must know how to use it correctly, especially when you are running it in a large loop. I will go over the concept using the example below.
1. DEFINE
First you must define a cursor as below.
cursor_city_country is the name of the cursor. There are three variables to store from the cursor, @city_name, @country_name and @city_id. The cursor will get the row data from the query defined under "CURSOR DEFINITION" section in the example above.
2. OPEN
Then you are opening a cursor as below:
By doing this, the cursor will start consuming SQL server's resources because the cursor for the first row data from the query is now "open".
3. FETCH & EVALUATE
The third step is that you will be "fetching" row data to the variables using the cursor.
Now the variables will be set with the data (in the order of SELECT query you defined above). Once you have these variables set, you can do any evaluation you want. In a real world, you will want to include this in the loop.
4. REPEAT with a LOOP
Once the cursor is opened and data is fetched to variables, you will want to do an evaluation in a loop using the next row data. Below is an example:
In the example above, the evaluation is just PRINT, but you willl be doing more complex evaluation in a real world. The important concept is that you want to set the loop condition as @@FETCH_STATUS = 0. If there is no more data to be fetched, then @@FETCH_STATUS will be set to 0. Therefore, you are running a WHILE loop until it is set to 0 (end of the query data).
5. GARBAGE COLLECTION (DEALLOCATION)
This is the most important part you must remember. When you are done with the cursor, you must deallocate resources that the cursor has consumed. Otherwise, the resources will not be released back to the server, potential for future crashes. Use the following code to deallocate the resources:
The code below is the complete version of the example:
Hope this helped! Thank you
Comments