How to see the value of a variable being used in an sql statement during a server trace?

Question

I have a stored procedure which has SQL statements that take variables. For example,

update customer set cust_id = @newcust_id where issuedate < @transferdate and cust_id = @oldcust_id

The values of the variables are assigned earlier in the SP.

How do I show the values of these variables during a server trace?

Answer 1

For debugging purposes you can trace them explicitly into the Profiler using a custom event, via sp_trace_generateevent:

declare @tracedata varbinary(8000);
set @tracedata = cast(@transferdate as varbinary(8000));
exec sp_trace_generateevent 82, 
        N'@transferdate', 
        @tracedata );
set @tracedata = cast(@oldcust_id as varbinary(8000));
exec sp_trace_generateevent 82, 
        N'@oldcust_id', 
        @tracedata);

You need to modify your Profiler session to monitor for the user User-Configurable Event Class, otherwise you won't see the generated events. I would also comment out the call to sp_trace_generateevent in production once the debugging is done. The call is not critically expensive (specially if there is no monitoring listening for it), but it should be removed none-the-less.

Updated

The exec call doe snot allow for a CAST to be inlined in the parameter list. I modified the code to show how to cast it before the exec. The value will be in the BinaryData column, in HEX representation (so 'daf' will show up as 0x646166).

Answer 2

Change the stored procedure to PRINT them before the UPDATE runs, or use the debugger built in to SSMS rather than Profiler.

Answer 3

If you are tracing you will catch all params in your trace.

If you want to see the last input from a given connection use DBCC INPUTBUFFER