title: Calling dynamic SQL Server stored procedure - expects parameter which was not supplied
body: <p>I'm trying to call a stored procedure, the name of which is provided by looping through a table. The stored procedure is in another database.</p>
  
  <p>However, I keep getting the message </p>
  
  <blockquote>
    <p>Procedure or function 'sp_KPI_People_Added_Count' expects parameter
    '@Period', which was not supplied.</p>
  </blockquote>
  
  <p>But it is being supplied.</p>
  
  <p>I have set the statement and parameter definition to <code>NVarChar</code> and prefixed them with <code>N</code>. I've also tried this on the parameters themselves but apparently that's not required.</p>
  
  <p>All the print output is as expected and if I call an execute with the text, it works fine.</p>
  
  <p>So I'm stuck.  Any pointers would be warmly welcomed.</p>
  
  <p>Thanks,</p>
  
  <p>Chris. </p>
  
  <pre><code>Declare @KPI_Value Decimal(14,4)
  Declare @StoredProcedure NVarChar(200)
  Declare @Periodic Char(1)
  Declare @ExecSql NVarChar(200)
  Declare @ParameterDefinition AS NVarChar(200)
  
  Declare KPI_Cursor CURSOR LOCAL FOR
      SELECT SProc, Periodic from KPI where Section = 2
      FOR UPDATE OF Current_Value
  
  Open KPI_Cursor
  
  Fetch Next From KPI_Cursor into @StoredProcedure, @Periodic
  Declare @Required_Period VARCHAR(5)
  SET @Required_Period='MTD'
  
  While @@FETCH_STATUS = 0 BEGIN
  Set @KPI_Value = 0  -- Have tried with and without this
  
      If @Periodic = 'Z' BEGIN
          SET @ExecSQL = N'Prod.dbo.' + @StoredProcedure;
          /* Specify Parameter Format */
          SET @ParameterDefinition =  N'@Period VarChar(5), @Result_Type VarChar(10), @Result Decimal(14,4) OUTPUT';
          /* Execute Transact-SQL String */
          print @ExecSQL
          print @ParameterDefinition
          print @Required_Period
          print @KPI_Value
          EXECUTE sp_executesql @ExecSQL, @ParameterDefinition, @Required_Period, 'Result', @KPI_Value Output 
      END
  
      Fetch Next from KPI_Cursor into @StoredProcedure, @Periodic
  END
  
  Close KPI_Cursor
  Deallocate KPI_Cursor
  </code></pre>
  
  <p>The stored procedures have parameters as follows:</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[sp_KPI_People_Added_Count] 
      @Period VarChar(5),     -- MTD or YTD
      @Result_Type VarChar(10),   -- Summary, Result or Detail
      @Result Decimal(14,4) OUTPUT    -- The returned result
  AS
  ....
  </code></pre>
  
  <p>The output is as follows:</p>
  
  <blockquote>
    <p>Prod.dbo.sp_KPI_People_Added_Count<br>
    @Period VarChar(5), @Result_Type VarChar(10), @Result Decimal(14,4) OUTPUT<br>
    MTD<br>
    0.0000<br>
    Msg 201, Level 16, State 4, Procedure sp_KPI_Cust_Added_Count, Line 0<br>
    Procedure or function 'sp_KPI_People_Added_Count' expects parameter '@Period', which was not supplied.</p>
  </blockquote>