Session Variable – Context_Info

Session is a powerful tool in any of the programming language.

--SQL-Server is not a full fledge programming language but it do supports session variable for current session or connection.

--It stores value of session in 128 byte of binary information.

--You can set or retrieve its value from CONTEXT_INFO column of the following system views

1. sys.dm_exec_requests

2. sys.dm_exec_sessions

3 .sys.sysprocesses

Now let’s have a look at example.

DECLARE @Fname VARCHAR(10), @Lname VARCHAR(10), @bVar binary(128)

SET @Fname='Company'

SET @Lname='IT'

SELECT @bVar=CONVERT(binary(6),@Fname)+CONVERT(binary(4),@Lname)

----- Set @bVar In CONTEXT_INFO Session-----

SET CONTEXT_INFO @bVar

---Now Select First Name And Last Name From Session Variable -

SELECT Fname = convert(varchar(10), substring(context_info, 1, 6))

FROM master..sysprocesses WHERE spid = @@spid

SELECT Lname = convert(varchar(10), substring(context_info, 7, 4))

FROM master..sysprocesses

WHERE spid = @@spid

@@spid is used to get the exact session ID. As I explained previously also, you can get the CONTEXT_INFO column in three system views so that you can query any of the views like:

SELECT context_info

FROM sys.dm_exec_sessions

WHERE session_id = @@SPID;

SELECT context_info

FROM sys.dm_exec_requests

WHERE session_id = @@SPID;

SELECT context_info

FROM sys.sysprocesses

WHERE spid = @@SPID;