Professional SQL Server™ 2005 Programming

A.10. System Functions

The system functions can be used to return information about values, objects and settings with SQL Server. The functions are as follows:

• APP_NAME

• CASE

• CAST and CONVERT

• COALESCE

• COLLATIONPROPERTY

• CURRENT_TIMESTAMP

• CURRENT_USER

• DATALENGTH

• FORMATMESSAGE

• GETANSINULL

• HOST_ID

• HOST_NAME

• IDENT_CURRENT

• IDENT_INCR

• IDENT_SEED

• IDENTITY

• ISDATE

• ISNULL

• ISNUMERIC

• NEWID

• NULLIF

• PARSENAME

• PERMISSIONS

• ROWCOUNT_BIG

• SCOPE_IDENTITY

• SERVERPROPERTY

• SESSION_USER

• SESSIONPROPERTY

• STATS_DATE

• SYSTEM_USER

• USER_NAME

A.10.1. APP_NAME

The APP_NAME function returns the application name for the current session if one has been set by the application as an nvarchar type. It has the following syntax:

APP_NAME()

A.10.2. CASE

The CASE function evaluates a list of conditions and returns one of multiple possible results. It also has two formats:

• The simple CASE function compares an expression to a set of simple expressions to determine the result.

• The searched CASE function evaluates a set of Boolean expressions to determine the result.

Both formats support an optional ELSE argument.

A.10.2.1. Simple CASE function:

CASE <input_expression>
   WHEN <when_expression> THEN <result_expression>
   ELSE <e lse_result_expression>
END

A.10.2.2. Searched CASE function:

CASE
    WHEN <Boolean_expression> THEN <result_expression>
    ELSE <else_result_expression>
END

A.10.3. CAST and CONVERT

These two functions provide similar functionality in that they both convert one data type into another type.

A.10.3.1. Using CAST:

CAST(<expression> AS <data_type> ...