Here I will explain what is cursor in sql server with example or simple cursor example in sql server and how to declare and use cursor in sql server. Cursor in sql server is used to retrieve set of data from table, loop through each record row by row, and modify the values based on requirements.
Description:
In previous articles I explained While loop example in sql server, nested while loop example in sql server, get only month and year from date in sql server, substring function in SQL server and many articles relating to SQL server. Now I will explain cursor in sql server with example.
To use cursor in sql server that syntax will be like as shown below
Syntax to Create Cursor
DECLARE cursorname CURSOR
FOR selectstatement -- like SELECT OrderID,CustomerID FROM Orders
OPEN cursor
FETCH tablerow FROM cursor
Process Fetched Tablerow
CLOSE cursor
DEALLOCATE cursor
|
If you observe above syntax we have different steps in cursor
Step 1: Declare Cursor
First we need to declare cursor name that will be used in cursor execution
Step 2: Select Statement
Select statement is used to get data from table to process with cursor
Step 3: Open Cursor
This statement is used to open the cursor to process select statement result set
Step 4: Fetch Rows
Once cursor is opened, rows can be fetched from the cursor one by one
Step 5: Close Cursor
Once our process finished we need to close the cursor
Step 6: Deallocate Cursor
We need to deallocate cursor to delete cursor definition and release resources associated with the cursor
We will check this with example for that first create one table UserDetails in your database and insert some 100 or 200 records or install northwind database in your server and use orders table it contains more than 500 records
Example
Write cursor script like as shown below and run it.
DECLARE ex_cursor CURSOR
FOR SELECT OrderID,CustomerID FROM Orders
DECLARE @oid INT
DECLARE @cname NVARCHAR(50)
OPEN ex_cursor
FETCH NEXT FROM ex_cursor INTO @oid,@cname
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT (CAST(@oid AS VARCHAR(5)) + '-' + @cname)
FETCH NEXT FROM ex_cursor INTO @oid,@cname
END
CLOSE ex_cursor
DEALLOCATE ex_cursor
|
Output:
When we run above query our results like as shown below
Demo
No comments:
Post a Comment