DOTNET: Sessions in SQL Server 2016

In .Net we have Session object which store and retrieve values for a user as user navigates ASP.NET pages in a Web application for that session.

With Sql Server 2016 we are getting the similar feature in Sql Server, where we can store multiple key and value pairs which are accessible throughout that session. The key and value pairs can be set by the sp_set_session_context system stored procedure and these set values can be retrieved one at a time by using the

SESSION_CONTEXT built in function.

SYNTAX of sp_set_session_context:

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

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 1: This example demonstrates how we can set the session context key named Employee Id with it’s value and retrieving this set keys value.

--Set the session variable EmployeeId value

EXEC sp_set_session_context 'EmployeeId', 1

--Retrieve the session variable EmployeeId value

SELECT SESSION_CONTEXT(N'EmployeeId') AS EmployeeId

ScreenShot:

Note: If we skip the Prefix N then we will get the Error.

From the result it is clear that the SESSION_CONTEXT function always expects the key parameter passed to it should of type NVarchar.

EXAMPLE 2: update the Session variable value

--Set session variable EmployeeName value

EXEC sp_set_session_context @key = 'EmployeeName',

@value='Uday'

--Retrieve the session variable EmployeeName value

SELECT SESSION_CONTEXT(N'EmployeeName') EmployeeName

--Re-Set the EmployeeName session variable value

EXEC sp_set_session_context @key ='EmployeeName',

@value='Rajakonda Uday'

--Retrieve the update session variable EmployeeName value

SELECT SESSION_CONTEXT(N'EmployeeName') EmployeeName

ScreenShot:

Example 3: Read Only Session Variable

--create a readonly session variable Actor

EXEC sp_set_session_context @key = 'Actor', @value='Number 1 Hero of Tollywood',

@read_only = 1

--Retrieve the session variable Actor value

SELECT SESSION_CONTEXT(N'Actor') Actor

ScreenShot

Example 4: Session variable can be accessed in stored procedure which is set somewhere outside.

Let us create a stored procedure GetCountry

CREATE PROCEDURE GetCountry

AS

BEGIN

    SELECT SESSION_CONTEXT(N'Country') Country

END


--create a readonly session variable Country
EXEC sp_set_session_context @key = 'Country', @value='India'

--Retrieve the session variable Country value
SELECT SESSION_CONTEXT(N'Country') Country