Session Context Information
Microsoft® SQL Server™ 2000 introduces the ability to programmatically associate up to 128 bytes of binary information with the current session or connection. Session context information enables applications to set binary values that can be referenced in multiple batches, stored procedures, triggers, or user-defined functions operating on the same session, or connection. You can set a session context by using the new SET CONTEXT_INFO statement, and then you can retrieve the context string from the new context_info column in the master.dbo.sysprocesses table.
Session context information differs from Transact-SQL variables, whose scope is limited to the current batch, stored procedure, trigger, or function. Session context information can be used to store information specific to each user or the current state of the application, which can then be used to control the logic in Transact-SQL statements.
The SET CONTEXT_INFO statement supports:
- A constant, with a maximum of 128 bytes, that is either binary or a data type that can be implicitly converted to binary.
- The name of a varbinary(128) or binary(128) variable.
SET CONTEXT_INFO cannot be specified in a user-defined function. You cannot supply a null value to SET CONTEXT_INFO because the sysprocesses table, where the information is stored, does not allow null values.
To get the current session context for the current connection, select the context_info column from the master.dbo.sysprocesses row whose SQL Server Process ID (SPID) is equal to the SPID for the connection. The SPID for the current connection is returned by the @@SPID function:
SELECT context_info
FROM master.dbo.sysprocesses
WHERE spid = @@SPID
The value in the context_info column is initialized to 128 bytes of binary zeros if SET CONTEXT_INFO has not yet been executed for the current connection. If SET CONTEXT_INFO has been executed, the context_info column contains the value set by the last execution of SET CONTEXT_INFO for the current connection. The context_info column is a varbinary(128) column.
This is an example of using session context information:
-- Set context information at start.
SET CONTEXT_INFO 0x1256698456
GO
-- Perform several non-related batches.
sp_who
GO
USE Northwind
GO
SELECT CustomerID
FROM Customers
WHERE City = 'London'
GO
-- Select context information set several batches earlier.
SELECT context_info
FROM master.dbo.sysprocesses
WHERE spid = @@spid
GO
SET CONTEXT_INFO does not support referencing expressions other than constants or variable names, such as functions. If you need to set the context information to the result of a function call, you must first place the function call result in a binary or varbinary variable:
DECLARE @BinVar varbinary(128)
SET @BinVar = CAST( REPLICATE( 0x20, 128 ) AS varbinary(128) )
SET CONTEXT_INFO @BinVar