Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

3 Answers 3

up vote 8 down vote accepted

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

share|improve this answer
    
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)); –  deutschZuid Mar 5 '12 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.. –  deutschZuid Mar 5 '12 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 –  Remus Rusanu Mar 5 '12 at 22:26
    
It's a typo sorry. It has one @ in the code –  deutschZuid Mar 5 '12 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. –  deutschZuid Mar 6 '12 at 2:18
show 1 more comment

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

share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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