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.

Syntax

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

Reference

CURSOR_STATUS
(
{ ‘local’ , ‘cursor_name’ }
| { ‘global’ , ‘cursor_name’ }
| { ‘variable’ , ‘cursor_variable’ }
)

Function Results

pic0

Example

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.
CLOSE emp
SELECT CURSOR_STATUS(‘global’,’emp’) AS ‘After Close’

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

–Remove the cursor from memory.
DEALLOCATE emp

Output

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

Advertisements
  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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: