Home > T-SQL > SQL SERVER – CURSOR_STATUS Cursor System Function

SQL SERVER – CURSOR_STATUS Cursor System Function

CURSOR_STATUS() is the cursor system functions in SQL Server that is used to check the status of current cursor. This is useful in an application to check the cursor status before invoking it. Especially, when we are working with one or more cursors in the application. This function requires two parameters that is given in the syntax section. We have to remember that this function is a non-deterministic. So, result may vary in different situations.

Status can be obtained for both local and global cursor.


CURSOR_STATUS(cursor scope, cursor name)
CURSOR_STATUS(Scope variable name, cursor variable name)


{ ‘local’ , ‘cursor_name’ }
| { ‘global’ , ‘cursor_name’ }
| { ‘variable’ , ‘cursor_variable’ }

Function Results



In this example I am going to declare a simple cursor and get its status in different state.
–Create a cursor.
DECLARE emp CURSOR  FOR SELECT EmpName FROM dbo.tblEmployee

–Display the status of the cursor before and after opening
SELECT CURSOR_STATUS(‘global’,’emp’) AS ‘After declare’

— opening the cursor
OPEN emp
SELECT CURSOR_STATUS(‘global’,’emp’) AS ‘After Open’

–closing the cursor.
SELECT CURSOR_STATUS(‘global’,’emp’) AS ‘After Close’

— invalid cursor name
SELECT CURSOR_STATUS(‘global’,’dept’) AS ‘isValid?’

–Remove the cursor from memory.


pic1This cursor function is really a useful function. I hope you enjoyed reading this blog post.

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: