Enabled debugging mode within the stored procedure without using parameter

In our previous article, we discussed that the stored procedure should contain a parameter call "@debug_mode" so that it can be debug in the development phase. But, some people might feel that it makes the stored procedure looks ugly. In this article, we are going to show you another way to enable the debugging in the development without having this parameter.

To store the information to the current session/connection, use the following command:

   SET CONTEXT_INFO 0x1000

To retrieve the value, use the following function:

   SELECT context_info()

context_info able to store upto 128 bytes of information. The only thing is that you must learn how to work with bitwise operator.

First thing you have to do with context_info is to define the meaning of each "box". For example, you need to implement 4 hidden features. Then, the value will look like "0x0000" where "0x" indicates it is hex value and you can't use these two character to store any information. But, the 4 digits after "0x", you are freely to define the meaning.

• 1st box - if "1" means enable the debugging mode. Otherwise, "0".
• 2nd box - could be defined as "1" to log down the process details.
• 3rd box - could be defined as "1" to print the runtime value in the IDE.
• 4th box - could be defined as "1" to rollback the process so that it won't disturb the test data.

Lastly, before you run your stored procedure, you have to call "set context_info 0x1001" to enable the debug mode and rollback the changes.

Some modification is required in your stored procedure:

declare
  @info varbinary(128),
  @debug_mode int,
  @rollback_data int

select @info = context_info()

if @info & 0x1000 = 0x1000
  select @debug_mode = 1
else
  select @debug_mode = 0

if @info & 0x0001 = 0x0001
  select @rollback_data = 1
else
  select @rollback_data = 0

(..contine your process here..)

References:
http://msdn.microsoft.com/en-us/library/ms187768.aspx
http://msdn.microsoft.com/en-us/library/ms176122.aspx