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?

3 Answers 3


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, 
        @tracedata );
set @tracedata = cast(@oldcust_id as varbinary(8000));
exec sp_trace_generateevent 82, 

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.


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

  • Here is the code I used to test this sp in SSMS: declare @@oldcust_id varchar(10) set @@oldcust_id = 'daf' exec sp_trace_generateevent 82, N'@oldcust_id', cast(@oldcust_id as varbinary(8000)); Mar 5, 2012 at 21:49
  • And for some reason, it gave me an error saying Msg 102, Level 15, State 1, Line 5 Incorrect syntax near '@oldcust_id'. I really can't see any problem with the statement. Is the comma or I don't know.. Mar 5, 2012 at 21:49
  • Is it realy @@oldcust_id in the code, or is that double @@ just a typo in your comment? Also, is better to post this as an edit to your original question, is difficult to read code in comment Mar 5, 2012 at 22:26
  • It's a typo sorry. It has one @ in the code Mar 5, 2012 at 22:28
  • I figured it. Looks like we don't need the last parameter. This is what I did: declare @@oldcust_id varchar(10) set @@oldcust_id = 'daf' exec sp_trace_generateevent 82, @@oldcust_id. Two changes here: 1) I removed the 3rd parameter. 2) I unquoted the second parameter. If your parameter is something other than a varchar, than cast it into a different variable and use the second variable instead of the original one. Mar 6, 2012 at 2:18

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


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.