Although SQL Server enables you to define local variables within a T-SQL batch or stored procedure, local variables do not retain values between batches or stored procedures. Unfortunately, SQL Server 2012 does not enable you to create user-defined global variables. However, you can simulate global variables by using the
CONTEXT_INFO setting, which allows you to store information in the
context_info column in the
sys.sysprocesses catalog view. A row in
sys.sysprocesses exists for every connection to SQL Server, so the data remains there until you disconnect from SQL Server.
context_info column is a
binary (128) column. You can store any data value in it with the
SET CONTEXT_INFO command, but you have to deal with hexadecimal ...