Structured Query Language

Kategorie: Blog - News: 0 - Abos: 0 - Hits: 3298 | feed abonieren feed lang ein sternein sternein sternein sternein stern

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_STATUSReturn type of @@FETCH_STATUS function is integer.Examples of @@FETCH_STATUS Function :Example 1 : Use of @@FETCH_STATUS functionDECLARE Customer_Cursor CURSOR FORSELECT ContactName FROM CustomersOPEN Customer_CursorFETCH NEXT FROM Customer_Cursor WHILE @@FETCH_STATUS = 0 BEGIN FETCH NEXT FROM Customer_CursorENDCLOSE Customer_CursorDEALLOCATE Customer_CursorAbove 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 :@@CURSOR_ROWSReturn type of @@CURSOR_ROWS function is integer.Examples of @@CURSOR_ROWS Function :Example 1 : Use of @@CURSOR_ROWS function in select clauseSELECT @@CURSOR_ROWSOutput 0Above 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 TeaAbove 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_ROWSCLOSE Product_CursorDEALLOCATE Product_CursorOutput -1 Now @@CURSOR_ROWS function returns -1 i.e. cursor is dynamic.

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 clauseSELECT REPLICATE('Syntax-Example', 3) OutputSyntax-ExampleSyntax-ExampleSyntax-ExampleAbove example returns repeated string 3 times by specified string.Example 2 : Use of REPLICATE function to display field value of table in a select clauseSELECT ContactName, REPLICATE(ContactName,2) 'Repeated Name' FROM    CustomersOutputContactName             Repeated NameClickson Andrew         Clickson Andrew Clickson AndrewAdie Addison              Adie AddisonAdie AddisonChristopher Cole         Christopher ColeChristopher ColeAbove 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 StockFROM   Products OutputProductName             StockRogide soild                 NULLSpeagesild                    95Zanse koeken               36Cho colade                   15Scottish                        NULLAbove example displays Null where both columns UnitsInStock and ReorderLevel values are same otherwise it displays value of UnitsInStock column.

CASE Expression
CASE expression in sql server with examples.CASE expression enables many forms of conditional processing to be placed into a SQL statement. By using CASE, more logic can be placed into SQL statements instead of being expressed in a host language or 4GL program.CASE is a deterministic i.e They return same value every time they are called with a specific set of values.CASE expression has 2 formates as listed below.Simple CASE function compares  an expression to a set of simple expressions.Searched CASE function evaluates a set of boolean expression .   Syntax of Simple CASE Expression :CASE input_expression     WHEN when_expression THEN result_expression      [ ...n ]      [         ELSE else_result_expression      ] ENDinput_expression is any valid sql server expression evaluated when using the simple .WHEN Boolean_expression is a Boolean expression evaluated when using the searched CASE format. Boolean_expression is any valid Boolean expression.n is a placeholder indicating that multiple WHEN when_expression THEN result_expression clauses, or multiple WHEN Boolean_expression THEN result_expression clauses can be used.THEN result_expression is a expression returned when input_expression equals when_expression evaluates to TRUE, or Boolean_expression evaluates to TRUE. result expression is any valid SQL Server expression.ELSE else_result_expression is a expression returned if no comparison operation evaluates to TRUE. If this argument is omitted and no comparison operation evaluates to TRUE, CASE returns NULL. else_result_expression is any valid SQL Server expression. The data types of else_result_expression and any result_expression must be the same or must be an implicit conversion.Examples of CASE Expression :Example 1 : Use of simple CASE expression function in select clause SELECT    CASE SUBSTRING('Sintax-Example',1,2)           WHEN 'Si' THEN 'Please correct your spelling. Don''t use si instead of sy. It is Syntax-                                                                       Example.'            WHEN 'Se' THEN 'Please correct your spelling. Don''t use se instead of sy. It is Syntax-                                                                     Example.'         ELSE 'Syntax-Example'  ENDOutputPlease correct your spelling. Don't use si instead of sy. It is Syntax-Example.Above example compares first 2 characters specified string and displays output based on comparision expression evaluates to true.  Example 2 : Use of searched CASE expression function in select clauseSELECT              CASE                       WHEN SUBSTRING('Sintax-Example',1,2) = 'Si' THEN 'Please correct your spelling.                                                                Don''t use si instead of sy. It is Syntax-Example.'                      WHEN SUBSTRING('Sintax-Example',1,2) = 'Se' THEN 'Please correct your spelling.                                                                Don''t use se instead of sy. It is Syntax-Example.'                      ELSE 'Syntax-Example'             ENDOutputPlease correct your spelling. Don't use si instead of sy. It is Syntax-Example.Above example compares first 2 characters specified string and displays output based on comparision expression evaluates to true.  

Codes für deine Homepage