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.
Best Answer
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:
If I know that it is only ever called from one location. Otherwise, I do it in the callee instead of the caller.
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.