Example
Return the session settings for a specified option:
SELECT SESSIONPROPERTY('ANSI_NULLS');
Definition and Usage
The SESSIONPROPERTY() function returns the session settings for a specified option.
Syntax
SESSIONPROPERTY(option)
SQL SESSION_USER Function
Example
Return the user name of the current user:
SELECT SESSION_USER;
Definition and Usage
The SESSION_USER function returns the name of the current user in the SQL Server database.
Syntax
SESSION_USER
SQL NULLIF Function
Example
Compare two expressions:
SELECT NULLIF(25, 25);
Definition and Usage
The NULLIF() function returns NULL if two expressions are equal, otherwise it returns the first expression.
Syntax
NULLIF(expr1, expr2)
SQL ISNUMERIC Function
Example
Tests whether the expression is numeric:
SELECT ISNUMERIC(4567);
Definition and Usage
The ISNUMERIC() function tests whether an expression is numeric.
This function returns 1 if the expression is numeric, otherwise it returns 0.
Syntax
ISNUMERIC(expression)
SQL ISNULL Function
Example
Return the specified value IF the expression is NULL, otherwise return the expression:
SELECT ISNULL(NULL, 'iampsp.com');
Definition and Usage
The ISNULL() function returns a specified value if the expression is NULL.
If the expression is NOT NULL, this function returns the expression.
Syntax
ISNULL(expression, value)
SQL IIF Function
Example
Return “YES” if the condition is TRUE, or “NO” if the condition is FALSE:
SELECT IIF(500<1000, 'YES', 'NO');
Definition and Usage
The IIF() function returns a value if a condition is TRUE, or another value if a condition is FALSE.
Syntax
IIF(condition, value_if_true, value_if_false)
SQL CURRENT_USER Function
Example
Return the name of the current user in the SQL Server database:
SELECT CURRENT_USER;
Definition and Usage
The CURRENT_USER function returns the name of the current user in the SQL Server database.
Syntax
CURRENT_USER
SQL CONVERT Function
Example
Convert an expression to int:
SELECT CONVERT(int, 25.65);
Definition and Usage
The CONVERT() function converts a value (of any type) into a specified datatype.
Tip: Also look at the CAST() function.
Syntax
CONVERT(data_type(length), expression, style)
SQL COALESCE Function
Example
Return the first non-null value in a list:
SELECT COALESCE(NULL, NULL, NULL, 'iampsp.com', NULL, 'Example.com');
Definition and Usage
The COALESCE() function returns the first non-null value in a list.
Syntax
COALESCE(val1, val2, ...., val_n)
SQL CAST Function
Example
Convert a value to an int datatype:
SELECT CAST(25.65 AS int);
Definition and Usage
The CAST() function converts a value (of any type) into a specified datatype.
Tip: Also look at the CONVERT() function.
Syntax
CAST(expression AS datatype(length))