SESSIONPROPERTY() Function in SQL Server

SESSIONPROPERTY() function :

This function in SQL Server is used to return the settings of the session that is specified in the argument section.

Features :

• This function is used to find the settings of the session state.
• This function comes under Advanced Functions.
• This function accepts only one parameter namely option.

Syntax :

SESSIONPROPERTY(option)

Parameter :

This method accepts only one parameter is given below as follows.

option -Specified option which is used for retrieving the session settings. It can be any one of the following values given below as follows.

ANSI_NULLS,
ANSI_PADDING,
ANSI_WARNINGS,
ARITHABORT,
CONCAT_NULL_YIELDS_NULL,
NUMERIC_ROUNDABOUT, and
QUOTED_IDENTIFIER.

Returns :

It returns the settings of the session that is specified in the argument section.

Example-1 :

Using SESSIONPROPERTY() function and getting the output.

SELECT SESSIONPROPERTY('ANSI_PADDING');

Output :

1

Here, 1 means that this session property is on.

Example-2 :

Using SESSIONPROPERTY() function and getting the output.

SET ANSI_WARNINGS OFF;
SELECT SESSIONPROPERTY('ANSI_WARNINGS');

Output :

0

Here, 0 is returned as we have turned off the session property.

Example-3 :

Using SESSIONPROPERTY() function and getting the output using a variable.

DECLARE @sp VARCHAR(20);
SET @sp = 'CONCAT_NULL_YIELDS_NULL';
SELECT SESSIONPROPERTY(@sp);

Output :

NULL

Here, NULL is returned as concatenated outputs are considered as NULL.

Example-4 :

Using SESSIONPROPERTY() function and 'ARITHABORT' as argument to get the output.

SELECT SESSIONPROPERTY('ARITHABORT');

Output :

0

Here, 0 is returned as this property is turned off.

Application :

This function is used to find the settings of the session that is specified in the argument section.