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 2008 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.

The 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 ...

Get Microsoft® SQL Server 2008 R2 Unleashed now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.