Static Cursor in SQL Server

The SQL Static Cursors are always read-only because the result set will store in tempdb. You can use this static cursor in SQL Server to move both forward and backward. For this SQL Server Static Cursor demonstration, we use the below-shown table, which holds 14 records

Static Cursor in SQL Server 1

Static Cursor in SQL Server Example

In this SQL cursor example, we will show you how to declare and open a static cursor in SQL Server. And here, we are using the DECLARE CURSOR statement.

USE [SQL Tutorial]
GO

DECLARE employee_cursor CURSOR STATIC  
    FOR SELECT * FROM [EmployeeTable]
OPEN employee_cursor  
FETCH NEXT FROM employee_cursor;

ANALYSIS

Within this cursor example, the below statement will declare the static cursor called employee_cursor for all the records in Employee table

DECLARE employee_cursor CURSOR STATIC
FOR SELECT * FROM [EmployeeTable]

It will open the declared cursor

OPEN employee_cursor

The next statement will fetch, or return the next record from the employee_cursor cursor.

FETCH NEXT FROM employee_cursor;

Although the employee table has 14 records, this SQL Server static cursor is retrieving one record. It is because FETCH NEXT will fetch only one record, and if you want all, then use Loops.

Static Cursor in SQL Server 2

See the SQL Server Static cursor is retrieving the second record only. If you observe closely, we are selecting only the FETCH statement because our employee_cursor is still in use, since we haven’t closed or deallocated it.

Static Cursor in SQL Server 3

Let me use the FETCH LAST option. It returns the Last record present in our table, and that is ID number 14.

Static Cursor in SQL Server 4

Next, we used the FETCH PRIOR option. It returns the row before the Last written record, and that is ID number 13 (before 14).

Static Cursor in SQL Server 5

Lastly, we used the FETCH FIRST option. Returns the first record present in our table, and that is ID number 1.

Static Cursor in SQL Server 6

Static Cursor in SQL Server Example 2

In our previous sql cursor example, we have shown how the cursor is fetching (or working) single row at a time. In this Static cursor in Sql Server example, we will use the WHILE LOOP to loop over the cursor elements and print them as output

SET NOCOUNT ON
-- Declaring the Variables 
DECLARE @EmpID INT,
        @EmpName VARCHAR(50),
        @EmpEducation VARCHAR(50),
 @EmpOccupation VARCHAR(50),
 @EmpYearlyIncome DECIMAL (10, 2), 
 @EmpSales DECIMAL (10, 2);

DECLARE employee_cursor CURSOR 
STATIC FOR 
 SELECT [ID]
       ,[Name]
       ,[Education]
       ,[Occupation]
       ,[YearlyIncome]
       ,[Sales]
 FROM EmployeeTable

OPEN employee_cursor
IF @@CURSOR_ROWS > 0
BEGIN 
 FETCH NEXT FROM employee_cursor 
                  INTO @EmpID, @EmpName, @EmpEducation,
        @EmpOccupation, @EmpYearlyIncome, @EmpSales
 WHILE @@FETCH_STATUS = 0
 BEGIN
 PRINT 'ID = '+ CONVERT(VARCHAR(10), @EmpID)+', Full Name = '+ @EmpName
 +', Education = '+ @EmpEducation +', Occupation = '+ @EmpOccupation 
 + ', Yearly Income = ' + CONVERT(VARCHAR(10),@EmpYearlyIncome)
 + ', Sales Amount = ' + CONVERT(VARCHAR(10),@EmpSales)
 
                FETCH NEXT FROM employee_cursor 
                              INTO @EmpID, @EmpName, @EmpEducation,
    @EmpOccupation, @EmpYearlyIncome, @EmpSales
 END
END
CLOSE employee_cursor
DEALLOCATE employee_cursor
SET NOCOUNT OFF 

Static Cursor ANALYSIS

Within this SQL Server Static cursor example, First, we used SET NOCOUNT ON stop the number of rows affected message from SQL Server Query. Next, we declared a few variables to hold the data coming from the Cursor. Then, we declared, and open the SQL static cursor called employee_cursor for all the records in Employee table

Next, we used the @@CURSOR_ROWS within the IF Statement to check whether there are any rows in the Cursor or not

IF @@CURSOR_ROWS > 0

It will fetch the next record from employee_cursor into already declared variables.

FETCH NEXT FROM employee_cursor 
           INTO @EmpID, @EmpName, @EmpEducation,
	       @EmpOccupation, @EmpYearlyIncome, @EmpSales

Next, we used the WHILE LOOP to loop over the cursor elements. And within the loop, FETCH_STATUS is used to check the status of the FETCH statement. Within the Loop, we used the Print statement to print the records row by row.

PRINT 'ID = '+ CONVERT(VARCHAR(10), @EmpID)+', Full Name = '+ @EmpName
	+', Education = '+ @EmpEducation +', Occupation = '+ @EmpOccupation 
	+ ', Yearly Income = ' + CONVERT(VARCHAR(10),@EmpYearlyIncome)
	+ ', Sales Amount = ' + CONVERT(VARCHAR(10),@EmpSales)

and then, we used the FETCH NEXT to get the next record from the cursor.

FETCH NEXT FROM employee_cursor 
           INTO @EmpID, @EmpName, @EmpEducation,
	        @EmpOccupation, @EmpYearlyIncome, @EmpSales

Lastly, we used the CLOSE, and DEALLOCATE statements to close, and deallocate the cursor.

CLOSE employee_cursor
DEALLOCATE employee_cursor
Static Cursor in SQL Server 7

Static Cursor in SQL Server Example 3

In our previous example of a SQL cursor, we showed how to use the WHILE LOOP to loop over the cursor elements and print them as output. In this Static cursor in the SQL Server example, we will add a select statement inside the while loop to display the items as the result set.

SET NOCOUNT ON
DECLARE @EmpID INT,
        @EmpName VARCHAR(50),
        @EmpEducation VARCHAR(50),
	@EmpOccupation VARCHAR(50),
	@EmpYearlyIncome DECIMAL (10, 2), 
	@EmpSales DECIMAL (10, 2);

DECLARE employee_cursor CURSOR 
STATIC FOR 
	SELECT [ID]
	      ,[Name]
	      ,[Education]
	      ,[Occupation]
	      ,[YearlyIncome]
	      ,[Sales]
	FROM EmployeeTable

OPEN employee_cursor
IF @@CURSOR_ROWS > 0
BEGIN 
	FETCH NEXT FROM employee_cursor 
                  INTO @EmpID, @EmpName, @EmpEducation,
		       @EmpOccupation, @EmpYearlyIncome, @EmpSales
	WHILE @@FETCH_STATUS = 0
	BEGIN
		PRINT 'ID = '+ CONVERT(VARCHAR(10), @EmpID)+', Full Name = '+ @EmpName
			+', Education = '+ @EmpEducation +', Occupation = '+ @EmpOccupation 
			+ ', Yearly Income = ' + CONVERT(VARCHAR(10),@EmpYearlyIncome)
			+ ', Sales Amount = ' + CONVERT(VARCHAR(10),@EmpSales)
		
                -- Display the result set
                SELECT @EmpID AS ID, @EmpName AS Name, @EmpEducation AS Education,
		   @EmpOccupation AS Occupation, @EmpYearlyIncome + 25000 AS YearlyIncome, 
                   @EmpSales + 1000 AS Sales
                
                FETCH NEXT FROM employee_cursor 
                              INTO @EmpID, @EmpName, @EmpEducation,
				   @EmpOccupation, @EmpYearlyIncome, @EmpSales
	END
END
CLOSE employee_cursor
DEALLOCATE employee_cursor
SET NOCOUNT OFF 

It is displaying each record as a separate result set. And also, it is reflecting the changes that we made (Yearly Income + 25000, Sales + 1000) in the SELECT Statement

Static Cursor in SQL Server 8

Let us see the Message tab to check the Print statement result of the cursor in SQL Server. As you can see, it is displaying the Yearly Income and Sales values as it is in the Employee table. It is because we haven’t changed any value in the Print statement.

Static Cursor in SQL Server 9