Advanced functions in SQL server

These all are the advance function in SQL server which are used to manipulate on the database with the help of these function we can get the current user name, user id, and more.

Advanced functions in SQL server

IFF() function in SQL: - 

"IIF() function" is a conditional function like if in any programming. IFF() function return the value if the condition is true or another value if a condition is FALSE. 

Syntax:-

IIF(condition, value_if_true, value_if_false)

ISNULL() function in SQL:- 

ISNULL() is used to give the specified value if the expression is NULL.

Syntax:-

ISNULL(expression, value)

The expression is tested to know that the given expression is NULL or not. If it's null then return the specified VALUE as shown in syntax.

ISNUMERIC() function in SQL Server:- 

ISNUMERIC() is used to test that given expression is numeric.

Syntax:-

ISNUMERIC(expression)

NULLIF() function in SQL Server:- 

NULLIF() function gives you NULL if two expressions are equal, otherwise, it returns the first expression.

Syntax:-

NULLIF(expr1, expr2)

SESSION_USER in SQL Server:- 

SESSION_USER function helps you to know about the current user in the SQL Server database.

Syntax:-

SESSION_USER

SESSIONPROPERTY() in SQL Server:- 

SESSIONPROPERTY() function in SQL gives the setting for a specified option for SQL SESSION.

Syntax:-

SESSIONPROPERTY(option)

Option In SESSIONPROPERTY()

•     ANSI_NULLS
•     ANSI_WARNINGS
•     QUOTED_IDENTIFIER
•     ARITHABORT
•     CONCAT_NULL_YIELDS_NULL
•     NUMERIC_ROUNDABOUT
•     ANSI_PADDING

SYSTEM_USER in SQL Server:- 

SYSTEM_USER  function does the same as SESSION_USER but the only difference is SYSTEM_USER gives the login name for the current user.

Syntax:-

SYSTEM_USER

USER_NAME() for SQL Server:- 

USER_NAME() function in SQL gives the name of the user depends on the specified id in the SQL database. If id is not specified then it will give the current user name.

Syntax:-

SYSTEM_USER