Static Cursors

A static cursor populates the result set at the time of cursor creation and query result is cached for the lifetime of the cursor. A static cursor can move forward and backward direction. A static cursor is slower and use more memory in comparison to other cursor. Hence you should use it only if scrolling is required and other types of cursors are not suitable.

No UPDATE, INSERT, or DELETE operations are reflected in a static cursor (unless the cursor is closed and reopened). By default static cursors are scrollable. SQL Server static cursors are always read-only.

1. SET NOCOUNT ON
2. DECLARE @Id int
3. DECLARE @name varchar(50)
4. DECLARE @salary int
5. DECLARE cur_emp CURSOR
6. STATIC FOR
7. SELECT EmpID,EmpName,Salary from Employee
8. OPEN cur_emp
9. IF @@CURSOR_ROWS > 0
10. BEGIN
11. FETCH NEXT FROM cur_emp INTO @Id,@name,@salary
12. WHILE @@Fetch_status = 0
13. BEGIN
14. PRINT 'ID : '+ convert(varchar(20),@Id)+', Name : '+@name+ ', Salary : '+convert(varchar(20),@salary)
15. FETCH NEXT FROM cur_emp INTO @Id,@name,@salary
16. END
17. END
18. CLOSE cur_emp
19. DEALLOCATE cur_emp
20. SET NOCOUNT OFF