SESSION_CONTEXT Build function in Sql server 2016

SESSION_CONTEXT Build function in Sql server 2016

As we know very well about the Session in ASP.Net. In asp.net , Session object is used to store the information of a particular user and that information can be accessible across the application.

Sql server 2016 provided similar feature like asp.net session feature. We can store multiple key and value pair information in Sql server 2016.  By using of sp_set_session_context procedure, we can set the key and value pair that can be accessible thought out the session and can get that information by using SESSION_CONTEXT function.

SYNTAX of sp_set_session_context(this is the procedure that can be used to set the key and value pair):

sp_set_session_context [ @key= ] 'key', [ @value= ] 'value' [ , [ @read_only = ] { 0 | 1 } ]

Where: @key parameter is of type SYSNAME, @value is of type SQL_VARIANT and @read_only parameter is of type BIT

SYNTAX of SESSION_CONTEXT(This is SQL server 2106 build in function that can be used to retrieved the key and value pair)

SESSION_CONTEXT(N'key')

Where:’key’ parameter to this function is of type SYSNAME and the return type of this function is SQL_VARIANT 

Example:- 

--Set the session variable UserId value

EXEC sp_set_session_context 'UserId', 786

--Retrieve the session variable UserId value

SELECT SESSION_CONTEXT(N'UserId') AS UserId

Result:-

UserId

786

--Retrieve the session variable UserId value

SELECT SESSION_CONTEXT('UserId') AS UserId

Msg 8116, Level 16, State 1, Line 9

Argument data type varchar is invalid for argument 1 of session_context function.

The above query will give error . The key value should be NVarchar.