title: Performance differences calling sp_executesql with dynamic SQL vs parameters
body: <p>Given:</p>
  
  <pre class="lang-sql prettyprint-override"><code>CREATE PROCEDURE [dbo].[my_storedproc]
    @param1 int, @param2 varchar(100)
  AS 
  &lt;&lt;whatever&gt;&gt;
  GO
  </code></pre>
  
  <p>Are there known performance differences between these different execution methods?:</p>
  
  <pre class="lang-sql prettyprint-override"><code>-- Method #1:
  declare @param1 int = 1
  declare @param2 varchar(100) = 'hello'  
  exec my_storedproc @param1, @param2
  
  -- Method #2:  
  exec my_storedproc @param1=1, @param2='hello'
  
  -- Method #3:  
  declare @param1 int = 1
  declare @param2 varchar(100) = 'hello'  
  declare @procname nvarchar(100) = N'my_storedproc @param1, @param2'
  declare @params nvarchar(4000) = N'@param1 int, @param2 varchar(100)'  
  exec sp_executesql @procname, @params, @param1, @param2
  
  -- Method #4:  
  declare @procname nvarchar(4000) = N'my_storedproc @param1=1, @param2=''hello'''
  exec sp_executesql @procname
  
  -- Method #5:  
  declare @procname nvarchar(4000) = N'my_storedproc 1, ''hello'''
  exec sp_executesql @procname
  
  -- Method #6:  
  declare @procname nvarchar(4000) = N'my_storedproc 1, ''hello'''
  exec (@procname)
  </code></pre>
  
  <p>"Why do you ask?" you ask?  I am trying to find a way to generically execute stored procedures entirely based upon metadata, the controlling stored procedure that will physically execute all the other configured (in metadata) stored procedures knows nothing about them other than what is defined in the metadata.  Within this controller SP, I cannot (in any practical sense) know and declare the specific physical parameters (with their required data types) required for every possible stored proc that might have to be called - I am trying to find a way to execute them entirely generically, while still hopefully maintaining decent performance (reusing query plans, etc).</p>