SQL Server – Programmatically Scan Stored Procedure Parameters

execution-planparametersql server

I have been trying to figure out how to scan a stored procs parameters from the execution plan. I often see most people suggest using the ParameterList within the execution plan xml. This works fine when there is just one statement that uses all of the parameters. However this is often not the case. The stored procedure parameters could be distributed over several queries or not used in a query at all.

In my examples below, I would like to know how to get the distinct parameters and their values from the example using sql xml functions. Also, you will notice that not all of the parameters are output into the ParameterLists. 

/***********************************************************************/
/*EXAMPLE: you have to scan all of the selects parameter lists to match up the parameter names to the proc parameters */
/***********************************************************************/
IF OBJECT_ID (N'dbo.TestFooProc') IS NULL
   EXEC('CREATE PROCEDURE dbo.TestFooProc AS SET NOCOUNT ON;');
GO

ALTER PROCEDURE dbo.TestFooProc (
    @p1 int,
    @p2 int,
    @p3 int = 8675309
) AS 
BEGIN
    --distribute the parameters over multiple selects
    SELECT * FROM master.dbo.[spt_values] sv WHERE [sv].[number] = @p2;

    -- other simulated queries ...

    SELECT * FROM master.dbo.[spt_values] sv WHERE [sv].[number] = @p1;

END
GO

EXEC dbo.TestFooProc @p1 = 5, @p2 = 22

SELECT [db_name] = DB_NAME([eps].[database_id]), qt.[text], qp.[query_plan]
FROM  sys.[dm_exec_procedure_stats] eps
CROSS APPLY sys.[dm_exec_sql_text](eps.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(eps.plan_handle) AS qp
WHERE [eps].[database_id] = DB_ID() 
    AND qt.[text] LIKE '%dbo.TestFooProc%'


RETURN  -- CLEANUP

IF OBJECT_ID (N'dbo.TestFooProc') IS NOT NULL
   DROP PROCEDURE dbo.TestFooProc; 
GO

Best Answer

Ok, after much digging into this I have determined this is not often possible. I will detail the following scenarios that break down the ability to get the original parameters from the execution plan.

  • If the parameter is not used in a where clause. Scenarios:
    • The parameter is placed into a local variable as a way to work around parameter sniffing
    • The parameter is used in a calculation in a local variable
  • If the parameter is used in a where clause but a function is used upon it
  • If the parameter is used in multiple sql statements in the stored proc it will appear in multiple parameter lists.

I just wanted a list of all of the original parameters passed into the stored proc and their values from the original plan creation so that I could recreate the call if need be. I know that I could obtain this information using extended events or profiler but I was trying to extract it from the query plans.

Related Question