set context_info -- Option to get parameter value during session across multiple batches

/*some time we need to use parameter values to be available for multiple bathces in same session
We can use context_info this case. Its value is available during current session and we can get its info from
sys.dm_exec_requests
sys.dm_exec_sessions
sys.sysprocesses

*/


--Example
DECLARE @var VARCHAR(50)

SET @var = 'My Name is Amish M Shah'

DECLARE @binvar BINARY(128)

SELECT @binvar = CONVERT(BINARY(128), @var)

SET context_info @binvar

go

/* New batch starts from here, context_info values which we set in previous batch is available in this batch also, from which we will get parameter value.*/
SELECT CONVERT(VARCHAR(50), context_info) parameter
FROM   sys.dm_exec_sessions
WHERE  session_id = @@SPID

No comments:

Post a Comment

Amazon Contextual Product Ads

Popular Posts