For a long time, web developers have been able to store session information. SQL Server has CONTEXT_INFO, which is a very poor implementation of a session variable. However, with SQL Server 2016 there is now a far more flexible SESSION_CONTEXT feature available.
The procedures associated with SESSION_CONTEXT enable values to be passed within batches that are within the same connection. These values can be edited or ‘locked’, depending upon supplied parameters.
For this demonstration a View will be used, to show that is effectively possible to pass parameters. This method will also work for triggers but it is just easier to implement a View for a simple test.
Listing 1: Test Data
CREATE DATABASE Session_Test; GO CREATE TABLE dbo.TestData ( ID INT IDENTITY(1, 1) NOT NULL , PartNo NVARCHAR(10) NOT NULL , PartName NVARCHAR(50) NOT NULL ); GO INSERT INTO dbo.TestData (PartNo , PartName ) VALUES ( N'00-123456', N'Test Product One' ) , ( N'01-98765', N'Test Product Two' ); GO CREATE VIEW dbo.ShowTestData AS SELECT ID , PartNo , PartName , SESSION_CONTEXT(N'test') AS [Test Context] , SESSION_CONTEXT(N'test2') AS [Test2 Context] , SESSION_CONTEXT(N'test3') AS [Test3 Context] FROM dbo.TestData; GO
Now to set the SESSION_CONTEXT values:
Listing 2: Setting SESSION_CONTEXT
EXEC sys.sp_set_session_context @key = N'test2' , @value = N'Test Value2' , @read_only = 0; EXEC sys.sp_set_session_context @key = N'test3' , @value = N'Test Value3' , @read_only = 1;
Listing 2 shows how to set a SESSION_CONTEXT value with sp_set_session_context.
The ‘@key’ parameter is a SYSNAME value, which cannot exceed 128 bytes. Oddly enough it does not have to be specified as NVARCHAR but if you don’t specify NVARCHAR when retrieving it (with SESSION_CONTEXT(N’name‘) then it will error with Argument data type varchar is invalid for argument 1 of session_context function.
The ‘@value’ parameter cannot exceed 8000 bytes – a considerable improvement over CONTEXT_INFO.
The ‘@Read_Only’ parameter, when set to 1 will prevent that session value from being changed.
Although the View refers to three different session context values, only two have been created. This is to demonstrate that selecting a value that does not exist will return NULL, instead of an error.
Listing 3: Select from the View
SELECT ID , PartNo , PartName , [Test Context] , [Test2 Context] , [Test3 Context] FROM dbo.ShowTestData; GO
Picture 1: Results from the View
The values associated with ‘test2’ and ‘test3’ are selected within the view, with NULL where it attempted to select a value for ‘test’.
Changing a SESSION_CONTEXT Value
The value associated with ‘test2’ can be changed by using sp_set_session_context and then selecting from the view again:
Listing 4: Update Session Value
EXEC sys.sp_set_session_context @key = N'test2' , @value = N'Test value updated' , @read_only = 0; GO SELECT ID , PartNo , PartName , [Test Context] , [Test2 Context] , [Test3 Context] FROM dbo.ShowTestData; GO
Picture 2: Results from View with Updated Session Value
Updating a Read_Only SESSION_CONTEXT
When a SESSION_CONTEXT has been created with ‘@read_only’ set to 1, an error will be raised if an attempt is made to change the value:
Listing 5: Attempting to Change a Read Only Value
EXEC sys.sp_set_session_context @key = N'test3' , @value = N'Test Value3 Updated' , @read_only = 1; GO
Executing the code in Listing 5 will result in the error message Msg 15664, Level 16, State 1, Procedure sp_set_session_context, Line 1 [Batch Start Line 34]
Cannot set key ‘test3’ in the session context. The key has been set as read_only for this session. This is because when this session context was set the ‘@read_only’ parameter was set to 1 and therefore prevents alteration. Try executing the code in Listing 5 with @read_only as zero and you will get the same error message.
A slightly more realistic example for using SESSION_CONTEXT is with Row-Level Security – introduce in SQL Server 2016. A basic example of this is in this Microsoft article on Row-Level Security.