sp-set-session-context-transact-sql.md

title	sp_set_session_context (Transact-SQL) | Microsoft Docs
ms.custom
ms.date	08/04/2017
ms.prod	sql-non-specified
ms.prod_service	database-engine, sql-database
ms.service
ms.component	system-stored-procedures
ms.reviewer
ms.suite	sql
ms.technology	database-engine
ms.tgt_pltfrm
ms.topic	language-reference
applies_to	Azure SQL Database SQL Server 2016 Preview
f1_keywords	sp_set_session_context sp_set_session_context_TSQL sys.sp_set_session_context sys.sp_set_session_context_TSQL
helpviewer_keywords	sp_set_session_context
ms.assetid	7a3a3b2a-1408-4767-a376-c690e3c1fc5b
caps.latest.revision	7
author	edmacauley
ms.author	edmaca
manager	craigg
ms.workload	Inactive

sp_set_session_context (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2016-asdb-xxxx-xxx-md]

Sets a key-value pair in the session context.

Transact-SQL Syntax Conventions

Syntax

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

Arguments

[ @key= ] 'key'
The key being set, of type sysname. The maximum key size is 128 bytes.

[ @value= ] 'value'
The value for the specified key, of type sql_variant. Setting a value of NULL frees the memory. The maximum size is 8,000 bytes.

[ @read_only= ] { 0 | 1 }
A flag of type bit. If 1, then the value for the specified key cannot be changed again on this logical connection. If 0 (default), then the value can be changed.

Permissions

Any user can set a session context for their session.

Remarks

Like other stored procedures, only literals and variables (not expressions or function calls) can be passed as parameters.

The total size of the session context is limited to 256 kb. If set a value that causes this limit to be exceeded, the statement fails. You can monitor overall memory usage in sys.dm_os_memory_objects (Transact-SQL).

You can monitor overall memory usage by querying sys.dm_os_memory_cache_counters (Transact-SQL) as follows: SELECT * FROM sys.dm_os_memory_cache_counters WHERE type = 'CACHESTORE_SESSION_CONTEXT';

Examples

The following example shows how to set and then return a sessions context key named language with a value of English.

EXEC sp_set_session_context 'language', 'English';  
SELECT SESSION_CONTEXT(N'language');  

The following example demonstrates the use of the optional read-only flag.

EXEC sp_set_session_context 'user_id', 4, @read_only = 1;  

See Also

CURRENT_TRANSACTION_ID (Transact-SQL)
SESSION_CONTEXT (Transact-SQL)
Row-Level Security
CONTEXT_INFO (Transact-SQL)
SET CONTEXT_INFO (Transact-SQL)