How to log value in SQL trace

Previous post explains how to check OUTPUT value of stored procedure in SQL Profiler. But, if you have nested stored procedure and want to see output parameter value, it's not easy. This is becasue the nested stored procedure is not RPC call and so is not captured in RPC:Completed event. Nested SP will be captured in SP:Staring and SP:Complated events. So even if you check RPC Output Parameter, the value will not be shown in SQL trace.

So how can we see the output value? If you use interactive tools such as SSMS, SQLCMD, this task is trivial. You can simply add PRINT or SELECT statement. But the problem is this statement does not print the value to SQL trace.

Obviously there is no standard trace event class that captures this kind of output, but we can use user defined event, so-called UserConfigurable event class, to get around this problem.

To create this event data from TSQL statement, we use sp_trace_generateevent which generates trace event with user defined text value (and binary value if specified).

The below GetNext SP has nested SP called GetNextNestedSP. Once the nest SP is called, we want to display OUTPUT parameter value to SQL trace.

CREATE PROCEDURE [GetNext] 
( @p1 NVARCHAR(32), @p2 NVARCHAR(32))
AS
BEGIN    
    DECLARE @out BIGINT
    DECLARE @text nvarchar(128)  

    EXEC [GetNextNestedSP] @p1, @p2, @out OUTPUT
                        
    SET @text = CONVERT(nvarchar(32), @out)                           
    EXEC master..sp_trace_generateevent  82, @text
                            
    -- cut --
END 

sp_trace_generateevent takes 3 parameters. First parameter is eventId for SQL trace. As MSDN says, this value is predefined and must be one of the event numbers from 82 through 91. ID 82 corresponds to [UserConfigurable:0] in SQL Profiler event class. 2nd parameter is text string that we want to send to SQL trace. This can be any string but there is a string length limitation (MSDN: 128 chars). 3rd parameter is optional binary data if needed. So when this SP (GetNext) is called from client application, how can we capture user defined event in SQL Profiler?