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.
No comments:
Post a comment