SQL ADVANCED Functions

In this topic, we described about the ADVANCED Functions with detailed example.

ADVANCED Functions that are mostly used in SQL are listed as follows –

Function	Description	Syntax
CURRENT_USER	It returns the name of the current user in SQL server database. It has no arguments.	CURRENT_USER
IIF	It returns one value, If specified expression is TRUE otherwise it returns another value. It has three arguments as expression, True_value, false_value.	IIF(expression, True_value, false_value)
ISNUMERIC	It returns an integer value in result set for specified expression. It returns 1, If specified value is numeric otherwise it returns 0. It has one argument as expression.	ISNUMERIC(expression)
SESSION_USER	It returns the name of the current user. It has no arguments.	SESSION_USER
SESSIONPROPERTY	It returns session settings for the specified option. It has one argument as option. The options that are used in SESSIONPROPERTY are listed as follows – ANSI_NULLS ANSI_PADDING ANSI_WARNINGS ARITHABORT CONCAT_NULL_YIELDS_NULL NUMERIC_ROUNDABOUT QUOTED_IDENTIFIER	SESSIONPROPERTY(option)
SYSTEM_USER	It returns the login name of the current user in the result set.	SYSTEM_USER
USER_NAME	It returns the data base user name based on the specified id. It has one optional argument as id_number.	USER_NAME(id_number)

ADVANCED Functions that are mostly used in SQL are explained as follows –

Function

Example

Output

CURRENT_USER

SELECT CURRENT_USER;

Now we can get output like as follows –

A
dbo

IIF

SELECT IIF(2*8 > 10, 'YES', 'NO') AS A;

Now we can get output like as follows –

A
YES

ISNUMERIC

SELECT ISNUMERIC(2020) AS A, ISNUMERIC('WE') AS B;

Now we can get output like as follows –

A	B
1	0

SESSION_USER

SELECT SESSION_USER;

Now we can get output like as follows -

A
dbo

SESSIONPROPERTY

SELECT SESSIONPROPERTY('ANSI_NULLS'), SESSIONPROPERTY('ANSI_PADDING');

Now we can get output like as follows -

A	B
1	1

SYSTEM_USER

SELECT SYSTEM_USER;

Now we can get output like as follows -

A
LAPTOP-TK9XXXXX\user_name

USER_NAME

SELECT USER_NAME(2);

Now we can get output like as follows -

A
guest