title: Getting parameters and their values of a stored procedure via DMVs
body: <p>I have a stored procedure using DMVs that returns some performance statistics on stored procedures.  In using this as a tool to look at SP performance, I found that some of my most complex stored procedures have multiple cached plans.  The stored procedures in question have a lot of parameters and their performance varies dramatically across their cached plans, therefore, I want to examine how these stored procedures are being called. Specifically the parameters that have values (other than their default).</p>
  
  <p>Is there a way to get the stored procedure command being called in the result set via another DMV?</p>
  
  <p>For example, I hope to be able to see something like: "Exec MyProcName @param1=value1, @param2=value2, @paramN=valueN"</p>
  
  <p>The second question I have on this subject is the multiple instances of the cached plans per stored procedure.  My thought is there could be multiple plans if the procedure are called with varying parameter values (enough variation to actually change or generate a new plan).  Am I on track here or way off base?</p>
  
  <p>Here is definition of my current stored proc that uses the sys.dm_exec_procedure_stats dmv:</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[bss_stored_procedure_performance]
  @dt_start as datetime = null
  ,@dt_end as datetime = null
  ,@db_name as varchar(100) = null
  ,@sp_name as varchar(100) = null
  ,@sort_col as varchar(25) = 'Avg Physical Reads'
  ,@sort_asc as bit = 0
  ,@record as bit = 0 
  AS
  BEGIN
  
  SET NOCOUNT ON;
  
  if @dt_start is null
      begin
          set @dt_start = CURRENT_TIMESTAMP - 3
          set @dt_end = CURRENT_TIMESTAMP
      end
  
  
  SELECT X.* FROM (
  SELECT CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME(database_id)END AS DBName
  ,OBJECT_SCHEMA_NAME(object_id,database_id) AS [Schema]  
  ,OBJECT_NAME(object_id,database_id)AS [Obj Name]
  ,type_desc as [Object Type]
  ,cached_time as [Cached dt]
  ,last_execution_time as [Last Execute dt]
  ,last_elapsed_time as [Last Duration]
  ,cast(last_elapsed_time*.000001 as decimal(9,2)) as [Last Duration / Seconds]
  ,execution_count as [Execution Count]
  ,total_worker_time / execution_count AS [Avg CPU]
  ,min_elapsed_time  AS [Min Duration]
  ,total_elapsed_time / execution_count AS [Avg Duration]
  ,max_elapsed_time AS [Max Duration]
  ,total_logical_reads / execution_count AS [Avg Logical Reads]
  ,total_logical_writes / execution_count AS [Avg Logical Writes]
  ,total_physical_reads  / execution_count AS [Avg Physical Reads]
  FROM sys.dm_exec_procedure_stats  
  where last_execution_time between @dt_start and @dt_end
  and ((@db_name is null) or (DB_NAME(database_id) = @db_name))
  and ((@sp_name is null) or (OBJECT_NAME(object_id,database_id) like '%' + @sp_name + '%'))
  ) X
  ORDER BY
  CASE WHEN @sort_col = 'Avg Physical Reads'
  AND @sort_asc = 1
  THEN [Avg Physical Reads] END ASC,
  CASE WHEN @sort_col = 'Avg Physical Reads'
  AND @sort_asc = 0
  THEN [Avg Physical Reads] END DESC,
  CASE WHEN @sort_col = 'Last Execute dt'
  AND @sort_asc = 1
  THEN [Last Execute dt] END ASC,
  CASE WHEN @sort_col = 'Last Execute dt'
  AND @sort_asc = 0
  THEN [Last Execute dt] END DESC
  
  
  
  print 'Date Range: ' + convert(varchar(50),@dt_start) + ' -- to -- ' +  convert(varchar(50),@dt_end)
  </code></pre>