FETCH_STATUS function in sql server

@@FETCH_STATUS function determines whether FETCH keyword has successfully retrieved a row from the current cursor. The value of @@FETCH_STATUS is undefined before any fetches have occurred on the connection.  

This function can have one of the three values:

Syntax of @@FETCH_STATUS Function :


Return type of @@FETCH_STATUS function is integer.

Examples of @@FETCH_STATUS Function :

Example 1 : Use of @@FETCH_STATUS function

SELECT ContactName FROM Customers
OPEN Customer_Cursor
FETCH NEXT FROM Customer_Cursor 
 FETCH NEXT FROM Customer_Cursor
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor

Above cursor displays each customer name one by one.


CURSOR_ROWS function in Sql Server

@@CURSOR_ROWS function returns number of rows currently in the last opened cursor. The number returned by @@CURSOR_ROWS is negative if the last cursor was opened asynchronously. Keyset-driver or static cursors are opened asynchronously if the value for sp_configurecursor threshold is greater than 0, and the number of rows in the cursor result set is greater than the cursor threshold.

Below is table that describes cursor status based on return type.

Syntax of @@CURSOR_ROWS Function :


Return type of @@CURSOR_ROWS function is integer.

Examples of @@CURSOR_ROWS Function :

Example 1 : Use of @@CURSOR_ROWS function in select clause



Above example returns 0 means currently cursor is not opened.
Now we will execute @@CURSOR_ROWS after cursor is opened.

Below is the code to create, open and execute cursor.

SELECT ProductName FROM Products 
OPEN Product_Cursor 
FETCH NEXT FROM Product_Cursor 

Output ProductName 

Above example returns output of executed cursor.

Below is the code to execute @@CURSOR_ROWS after cursor is executed. After that cursor is closed and deallocated.

CLOSE Product_Cursor
DEALLOCATE Product_Cursor

-1 Now 

@@CURSOR_ROWS function returns -1 i.e. cursor is dynamic.



REPLICATE function of sql server with examples.

REPLICATE is used to repeat a string with specified number of times. 

Syntax of REPLICATE Function :

REPLICATE ( character_expression ,integer_expression )

character_expression is a string to repeat. 

integer_expression is number of times the character_expression to be repeated.

Return type of REPLICATE function is varchar data type.  

Examples of REPLICATE Function :

Example 1 : Use of REPLICATE function in select clause

SELECT REPLICATE('Syntax-Example', 3) 


Above example returns repeated string 3 times by specified string.

Example 2 : Use of REPLICATE function to display field value of table in a select clause

SELECT ContactName, REPLICATE(ContactName,2) 'Repeated Name' 
FROM    Customers

ContactName             Repeated Name
Clickson Andrew         Clickson Andrew Clickson Andrew
Adie Addison              Adie AddisonAdie Addison
Christopher Cole         Christopher ColeChristopher Cole

Above example repeats customers name 2 times from customers table.


NULLIF Function

NULLIF function in sql server with examples.

NULLIF function returns null value if the two specified expressions are equivalent.

NULLIF is equivalent to a searched CASE function in which the two expressions are equal and the resulting expression is NULL.

NULLIF returns the first expression if the two expressions are not equivalent. If the expressions are equivalent, NULLIF returns a null value of the type of the first expression.

Syntax of NULLIF Function :

NULLIF (expression1, expression2)

expression1 and expression2 are any valid sql server expression. It can be constant, column name, function, subquery, or any combination of arithmetic, bitwise, and string operators.

Return type of NULLIF function is same as expression1.

Examples of NULLIF Function :

Example 1 : Use of NULLIF function in select clause 

SELECT ProductName, NULLIF(UnitsInStock,ReorderLevel) AS Stock
FROM   Products 


ProductName             Stock
Rogide soild                 NULL
Speagesild                    95
Zanse koeken               36
Cho colade                   15
Scottish                        NULL

Above example displays Null where both columns UnitsInStock and ReorderLevel values are same otherwise it displays value of UnitsInStock column.