SQL Cursor Syntax - Quick Tech Bits

 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.