Cursor:
- Is used to do a different processing row by row of the select query
- The column names displayed need to be formatted or validated or processed before showing output. This process can give different results per row.
- Hence cursor is used
Eg: Showing culture specific / language specific outputs for the same data. Here cursor can be used
Eg: Creating a comma-separated string for a given table data
- TIP: Imagine you are working with a file when writing the cursor syntax. Makes it easier to visualize and understand the syntax
- File is created, then opened, then processed line-by-line, then closed and removed from memory.
- The same holds for a cursor
- SYNTAX
1. DECLARE <cursorName> CURSOR FOR
2. <select query>
3. OPEN <cursorName>
4. FETCH NEXT FROM <cursorName>
5. INTO <variable names one each for selected columns>
6.
7. <your logic for processing>
8.
9. CLOSE <cursorName>
10. DEALLOCATE <cursorName>
*** CURSOR SHOULD ALWAYS BE USED AS THE LAST RESORT
*** DUE TO PERFORMANCE CONCERNS
Check this repo for an example script of the MoviesDb and run the Cursor example.
The script files below should be run in Microsoft SQL Management Studio / Visual Studio in the same order.
MoviesDB Script: Creates a MoviesDB database with sample data
Cursor Script: Contains an example for cursor & how it can be replaced with a normal SQL query.
Hope this helps.
