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> ...

Get Professional SQL Server™ 2005 Programming now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.