Variable Scope and CONTEXT_INFO

In SQL Server the variable which we declare in the block has scope to that block only, when you try to access that variable outside that scope, you won’t be able to access it. Let see with an example

Here we are creating two blocks, in first block we are declaring a variable @tempInt of INT type and initializing it with 5. And in second block, we are just trying to print the value of that variable.

declare @tempInt int

set @tempInt = 5

print @tempInt

go --Here we finished our first block

print @tempInt

go --Here we finished our Second block

5

Msg 137, Level 15, State 2, Line 1

Must declare the scalar variable "@tempInt".

From the output we can see the first Print which is present in first block where the variable was declared and initialized display its value but which we try to display the value of variable in second block it fires error saying, declare variable “@tempInt.

Conclusion: The scope of variable exists only to the block where it gets declared.

This problem can be solved using CONTEXT_INFO, context_info is a variable specific to a session of type varbinary(128), to access the value of context_info we have a function context_info(). You can also set your own value in context_info variable.

Session variable storing Varchar value

declare @strSource varbinary(50)

select @strSource = convert(varbinary(50),'My testing')

set context_info @strSource

declare @strDesti varchar(50)

select @strDesti =  cast(cast(context_info() as binary(50)) as varchar)

print @strDesti       

My testing

Session variable storing int value

declare @strSourceInt varbinary(50)

select @strSourceInt = convert(varbinary(max),387)

set context_info @strSourceInt

declare @strDestiInt int

select @strDestiInt = cast(cast(context_info() as binary(4)) as int)

print @strDestiInt

387

Session Variable scope

declare @strDesti varchar(50)

select @strDesti =  cast(cast(context_info() as binary(50)) as varchar)

print @strDesti       

declare @strSourceInt varbinary(50)

select @strSourceInt = convert(varbinary(max),300)

set context_info @strSourceInt

declare @strDestiInt int

select @strDestiInt = cast(cast(context_info() as binary(4)) as int)

print cast(@strDestiInt as varchar) + ' from Ist Batch'

go      --Here we finished first block

print cast(cast(cast(context_info() as binary(4)) as int) as varchar) + ' from IInd Batch'

declare @strSourceInt varbinary(50)

select @strSourceInt = convert(varbinary(max),400)

set context_info @strSourceInt

declare @strDestiInt int

select @strDestiInt = cast(cast(context_info() as binary(4)) as int)

print cast(@strDestiInt as varchar) + ' New value from IIst Batch'

go    --Here we finished second block

print cast(cast(cast(context_info() as binary(4)) as int) as varchar) + ' from IIIrd Batch'

go    --Here we finished third block

300 from Ist Batch

300 from IInd Batch

400 New value from IIst Batch

400 from IIIrd Batch

So from the output we can see that the first value which gets assigned to context_info variable was 300 and it is also accessable in second block too as we saw in second line of output then we modified the value of context_info variable in third block to 400, and again in third block we can access the modified value of context_info.

Conclusion: CONTEXT_INFO retains its value across blocks and u can also set new value to context info in any block.

When programmer do code, nothing annoys them so much.. J

Post Reference: Vikram Aristocratic Elfin Share