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 :

@@FETCH_STATUS

Return type of @@FETCH_STATUS function is integer.

Examples of @@FETCH_STATUS Function :

Example 1 : Use of @@FETCH_STATUS function

DECLARE Customer_Cursor CURSOR FOR
SELECT ContactName FROM Customers
OPEN Customer_Cursor
FETCH NEXT FROM Customer_Cursor 
WHILE @@FETCH_STATUS = 0 
BEGIN
 FETCH NEXT FROM Customer_Cursor
END
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor

Above cursor displays each customer name one by one.

FETCH_STATUS function in sql server

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 :

@@CURSOR_ROWS

Return type of @@CURSOR_ROWS function is integer.

Examples of @@CURSOR_ROWS Function :

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

SELECT @@CURSOR_ROWS

Output 
0

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.

DECLARE Product_Cursor CURSOR FOR 
SELECT ProductName FROM Products 
OPEN Product_Cursor 
FETCH NEXT FROM Product_Cursor 

Output ProductName 
Tea

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.

SELECT @@CURSOR_ROWS
CLOSE Product_Cursor
DEALLOCATE Product_Cursor

Output 
-1 Now 

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

CURSOR_ROWS function in Sql Server

REPLICATE Function

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) 

Output

Syntax-ExampleSyntax-ExampleSyntax-Example

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

Output

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.

REPLICATE Function

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 

Output

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.

NULLIF Function