T-SQL Cursor Usage

Cursor allows us to perform line-by-line operations within the returned values in SQL Server. We make this structure, which we use frequently in programming languages, thanks to the cursor in the database. We can read row by row data according to the condition we specify with the WHILE loop between the records we receive with the SELECT query. In the example below, you can see how this process is done, which is explained with comment lines.

DECLARE @Name NVarChar(Max), @Phone NVarChar(Max) -- Define variable(s) to get the returned ID value in Cursor 

DECLARE CUR_DIRECTORY CURSOR FOR -- Define the cursor 

 SELECT 'John', '123456789'
 UNION SELECT 'David', '987654321'
 UNION SELECT 'Ashley', '00112233' -- Write the query of the values to return in the cursor 

 OPEN CUR_DIRECTORY -- Open the cursor 
 FETCH NEXT FROM CUR_DIRECTORY INTO @Name, @Phone -- Assigning the returned values in the cursor to the variables. Columns are passed to variables respectively 
 WHILE @@FETCH_STATUS = 0 BEGIN -- Checking the fetch status. A value of 0 means that the previous fetch was successful, and this rule means continue as long as there are no errors. 
 PRINT @Name + ': ' + @Phone; -- Print the values or whatever you want to do... 
 FETCH NEXT FROM CUR_DIRECTORY INTO @Name, @Phone -- Move on to the next record 
 END
CLOSE CUR_DIRECTORY -- Close the cursor 
DEALLOCATE CUR_DIRECTORY -- With CLOSE, our Cursor closes but remains in memory. Delete Cursor from memory with DEALLOCATE

You may also like...