Is it possible to record incoming parameter values in a procedure call while tracing in SQL Server Profiler?

Question

Using SQL Server Profiler (I'm on SQL Server 2012), I'm trying to generate a useful trace that shows the parameter values, not just the SQL with variable names. The stored procedure walks through a gross amount of Inventory data to generate some extremely valuable results, and I'm trying to document the existing behaviour, so I can unit test it, define it exactly, and then refactor it into something sane.

I have a stored procedure that does a 54-parameter sub-procedure execute, inside a loop where the stored procedure creates a cursor then does a while loop. Here's a simplified view:

CREATE PROCEDURE 
   [dbo].[OuterProcedure]       
   (  @ProductCode varchar(8),          
     -- 41 more parameters omitted
   )
AS            
  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED   
  SET NOCOUNT ON           
 DECLARE @AboutFourHundredLocalvariables -- omit about 400 local variable declarations.
 -- OMIT ABOUT 10 temporary table declarations.
 DECLARE  aCursor CURSOR FAST_FORWARD FOR         
   SELECT [ID],bkno,  -- about 40 fields omitted.
              FROM vwVeryComplexViewThatDoesALotOfVeryBrutalJoins         
              WHERE  (about_80_boolean_expressions AND omitted_here)
        ORDER BY some,keys,like,this

OPEN aCursor          
FETCH NEXT FROM aCursor /* Get First Record */         
    INTO @ID, @about_40_fields,.... 
WHILE (@@FETCH_STATUS = 0) AND         
          ( @About80MoreBooleanExpressionsHere)  
BEGIN   /* 1 */            
     -- about 700 lines of logic, math and if-parameter-this-then-that
     -- stuff omitted
            EXEC  @ConsiderItem = 
                      InnerProcedureCallWithinLoop
                                            @from_locn,        
                        @About53PARAMSOMITTED,
                                                ...

    FETCH NEXT FROM CurInventory /* Get Next Record */       
       INTO @ID,@MoreStuff,...    
END                   
CLOSE CurInventory          
DEALLOCATE CurInventory        

How would I get a trace to show me all the parameter values passed to InnerProcedureCallWithinLoop? There are 54 parameters. Do I have to write essentially "54 lines of debug-printfs" inside my SQL or can I dump all the parameter values of a procedure call while doing an SQL trace some how?

When I get a trace right now, I get this output:

EXEC  @ConsiderItem = InnerProcedureCallWithinLoop  @from_locn,        
                        @About53ParmsOmitted

What I'd like to know is that @from_locn = 1 and @About53ParmsOmitted = 'hello world' and so on.

This doesn't tell me the actual value of the parameter @from_locn. In the case of that first parameter, it's passed in to my top level stored procedure, so I know it's 0, or 1, as the case may be. However about 40 of the 43 params in that inner procedure come from the FETCH NEXT FROM aCursor operation inside a WHILE loop.

Right now tracing tells me How many times InnerProcedureCallWithinLoop gets invoked, and how long each one took, but not what the values of the parameters to that call were. If I could somehow get "runnable standalone SQL scripts" that replicate some corner case I find within my code, while tracing through these scripts, setting up these gross functions (I know, 54 parameters, that's really gross, but I didn't write them!) could take me an hour of typing just to build an SQL script that lets me invoke this corner-case myself, outside this big gargantuan snarl of SQL Server stored procedures.

This is all part of an effort to drill down to an SQL expression and build scripts that can probe these complex stored procedures.

Update I found an RPC "Output Param" recording option, but not an "RPC IN PARAM" recording option.

Answer 1

I'll bite the bullet and tell you that such a trace cannot be set up, because it is not the [perceived] purpose of traces. I have always done it this way:

WHILE (@@FETCH_STATUS = 0) AND
            ( @About80MoreBooleanExpressionsHere)
BEGIN /* 1 */
    -- about 700 lines of logic, math and if-parameter-this-then-that
    -- stuff omitted
      INSERT InnerProcedureCallWithinLoop__TraceTable
              VALUES (@from_locn, @About53PARAMSOMITTED
      EXEC @ConsiderItem =
            InnerProcedureCallWithinLoop
                  @from_locn,
                        @About53PARAMSOMITTED,
...

If I know that it is only ever called from one location. Otherwise, I do it in the callee instead of the caller.

ALTER PROC InnerProcedureCallWithinLoop
    @from_locn int,
    @About53PARAMSOMITTED ...
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET NOCOUNT ON;
INSERT InnerProcedureCallWithinLoop__TraceTable VALUES (@from_locn, @prm2, @prm3....
--- rest of proc

This is obviously different from using a trace, which is able to capture events even if they started and never finished (faulty parameters, rolled back transactions). If that is your problem, you need to look at CLR or email methods to externalise the captured output.