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.

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.

share|improve this question
    
It isn't just the 54 parameters that are gross :-) All that looping to call a cursor. That's gross :-) Have you played with SQL Sentry's Plan Explorer? You can use the free version or use a trial of the full version and that can help you if you have a call with all the parameters - sqlsentry.net/plan-explorer/sql-server-query-view.asp –  Mike Walsh Nov 30 '12 at 21:40
    
Can you tell us which events you are capturing? –  Mike Walsh Nov 30 '12 at 21:41
2  
The only time the profiler catches that data (I think) is when you call it? Try setting up an audit table with all those params, and just do an insertinto before looping your cursor? –  jcolebrand Nov 30 '12 at 21:42
    
RPC:Completed shows parameter values but at the outer level. –  Mike Walsh Nov 30 '12 at 21:42
3  
+1 for the question, as recompense for your pain. If I could, I'd +1 again for posting the question without expletives. –  Mark Storey-Smith Dec 1 '12 at 0:29
show 1 more comment

1 Answer

up vote 6 down vote accepted

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.

share|improve this answer
    
That's what I thought. –  Warren P Dec 1 '12 at 13:38
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.