---

Search posts under SQL Server:

---

Cursor example in SQL Server

How to create a cursor in SQL Server?

To know how to create basic cursor, and what is cursor, types, scope etc. please read this post. To know how to create scroll cursor, read this post.

In this post, we shall understand how to create a cursor in SQL Server and store it's data into a temporary table variable and then take output from it.

Let's see this with example.

-- Declare variables to hold cursor data for each record
DECLARE @AutoIdC int
DECLARE @FirstNameC varchar(50)
DECLARE @LastNameC varchar(50)

-- declare table to hold temporary data
DECLARE @myTable as TABLE
(
    PersonId int,
    FullName varchar(100)
)

-- declare cursor
DECLARE myCursor CURSOR FOR 
    SELECT AutoId, FirstName, LastName FROM PersonalDetails 
                   WHERE AutoId > 1100 -- put more where clause if any

-- open
OPEN myCursor

-- fetch records from the cursor
FETCH NEXT FROM myCursor INTO @AutoIdC, @FirstNameC, @LastNameC

WHILE @@FETCH_STATUS = 0
BEGIN

    INSERT INTO @myTable(PersonId, FullName) VALUES 
                     (@AutoIdC, @FirstNameC + ' ' + @LastNameC)

    FETCH NEXT FROM myCursor INTO @AutoIdC, @FirstNameC, @LastNameC
END

CLOSE myCursor -- must to close
DEALLOCATE myCursor -- must to deallocate

-- Filter the output from the temp table if needed or just get all 
SELECT * FROM @myTable WHERE FullName LIKE 'S%'

In above code snippet, we have done following

1. We have first declared three variables that will hold data from the cursor temporarily.
2. Then we have declared a temp variable that will hold cursor result set data.
3. Then we have declared a cursor for the AutoId, FirstName, LastName columns from the PersonalDetails table.
4. Then we have opened the cursor
5. Then called the FETCH statement that will fetch 1^st record from the cursor into declared variables in point 1 above.
6. To iterate through each records (rows) of the cursor, we have started WHILE loop that will run till @@FETCH_STATUS is 0 that means till there are records into the cursor result set.
7. Inside the WHILE loop we have inserted that particular row record from the variable into the temp table
8. After inserting variable values into temporary variable, we have again called the FETCH statement that will retrieve next row value into the variables declared in point 1.
9. This iteration goes on till @@FETCH_STATUS is 0
10. At last we are closing the cursor
11. and then deallocating the cursor
12. At last we have filtered the table variale data that will list filtered record from the temp table.

Output