title: Dynamic sql query with stored procedure and table variable
body: <p>I want to call a stored procedure dynamically as my names of the procedures are stored somewhere, also I need to store the result of that procedure into a table variable. Hence I had to write following sql code,</p>
  
  <p>In following code
      @tblEmailIds is the table variable which I want to store the result of SP in
      @tempEmailSource is the name of the procedure
      @tempRecipientsIdsCSV is the first argument that my SP is accepting
      @ObjectMasterId is the second argument that SP is accepting (optional)</p>
  
  <pre><code>DECLARE @tempTypeName NVARCHAR(100), 
              @tempTypeId INT,
              @tempEmailSource NVARCHAR(100),
              @tempRecipientsIdsCSV NVARCHAR(MAX),
              @tempIsObjectSpecific BIT,
              @sqlQuery NVARCHAR(MAX) = 'INSERT INTO @tblEmailIds '
  SELECT TOP 1 @tempTypeName = NAME, 
  @tempTypeId = Id,         
  @tempEmailSource = EmailListSourceName 
  FROM @tbleRecipientsTypes WHERE IsEmailIdsFetched = 0
  
      SELECT @tempRecipientsIdsCSV = SUBSTRING(
      (SELECT ',' + CAST(RT.EmailRecipientId AS NVARCHAR(50))
      FROM @tbleRecipientsTypes RT WHERE RT.Id = @tempTypeId
      ORDER BY RT.EmailRecipientId
      FOR XML PATH('')),2,200000)
  
      SELECT @tempRecipientsIdsCSV
  
  
      SET @sqlQuery = @sqlQuery + 'EXEC ' + @tempEmailSource +' ' +''''  +    @tempRecipientsIdsCSV +''''
      IF (@tempIsObjectSpecific = 1)
      BEGIN
          SET @sqlQuery = @sqlQuery + ' ' + @ObjectMasterId
      END
  
      PRINT @SQLQUERY
  
      EXECUTE SP_EXECUTESQL 
      @SqlQuery,'@IdsCSV NVARCHAR(MAX) OUTPUT,  
      @ObjectMasterId INT = NULL OUTPUT', @tblEmailIds
  </code></pre>
  
  <p>I am getting the following error</p>
  
  <blockquote>
    <p>Msg 214, Level 16, State 3, Procedure sp_executesql, Line 6 Procedure
    expects parameter '@params' of type 'ntext/nchar/nvarchar'.</p>
  </blockquote>