title: Debugging queries inside stored procedures
body: <p>Consider this stored procedure. The actual content of procedure doesn't matter, I am using it only for the sake of the example:</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[temp]
        @value1 varchar(50),
        @value2 varchar(50),
        @value3 varchar(50)
  as      
  begin
      Select *
      From valuesTable (nolock)
            inner join valuesTable2 RL (nolock)
                  on (ValuesTable.ID = RL.RuleId and RL.Type = 'Something')     
      where @value1 = ValuesTable.RuleVal02
      and cast(@value2 as float) &gt; cast(ValuesTable.RuleVal03 as float) and cast(@value2 as float) &lt; cast(ValuesTable.RuleVal04 as float)
      --and (@value3 = ValuesTable.RuleVal05)
      and (@value3 = ValuesTable.RuleVal05 or ValuesTable.RuleVal05 = -1)
  end
  </code></pre>
  
  <p>Now imagine that this (not very complex function) isn't working. I already know how to debug it both from Visual Studio and from SQL Server Management Studio, however, both cases are lacking:</p>
  
  <p>The main failing point of this function is the big query that it executes. What I'd like to do then, is to take this query, copy it to a new query window and start executing it and debug it by modifying its various parts.</p>
  
  <p>The basic way to debug that query would be copy it to a new query window, get the parameters from the executing code and then manually replace all the <code>@variables</code> with their actual value. That works, but it seems like a very unnecessary work to me and ideally I'd like to get the query, as it is executed on the server - With literal values instead of the parameters, e.g:</p>
  
  <pre><code>where 'actualValue' = ValuesTable.RuleVal02
  and cast(4.2 as float) &gt; cast(ValuesTable.RuleVal03 as float) and cast(4.2 as float) &lt; cast(ValuesTable.RuleVal04 as float)...
  </code></pre>
  
  <p>Since it sounded like something I can only achieve from a profiler, I launched it. Then I configured the events to capture <code>SP:StmtStarting</code> event, to see the statements executed from stored procedures. To my surprise, however, the statements that I see in the profiler, still show with their parameter and not the actual literal value.</p>
  
  <p>Is a way I could easily copy the actual query that is executed in a stored procedure with the parameters replaced to the literal value of the parameter? Is there a flag in the profiler to do so? Or is there another way?</p>