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.