Summary -

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