title: SQL select result of stored procedure into a comment
body: <p>I am return a result set from a stored procedure (multiple columns). I am able to populate values for columns from functions (<code>getContactDisplayName</code>), e.g.:</p>
  
  <pre><code>SELECT
      (SELECT [dbo].getContactDisplayName([Form_Response_ID])) AS Submitted_By
  FROM 
      foo_Table;
  </code></pre>
  
  <p>I would like to do the exact same thing with a stored procedure (<code>sp_GetFormAnswer</code>), e.g.:</p>
  
  <pre><code>SELECT 
      (SELECT EXEC sp_GetFormAnswer @FieldName = 'Submission_Data') AS Answer
  FROM 
      foo_Table;
  </code></pre>
  
  <p>Is this possible? If so, why not? What are my alternatives? I do have to use a stored procedure, as that stored procedure calls dynamic SQL (which uses <code>exec</code>, which functions cannot do).</p>
  
  <p>Note - the code is tested, populating columns from functions works fine, as does the stored procedure, on its own. Just not inside the select.</p>
  
  <p>EDIT: My actual, full, working, stored procedure (sorry, it's not on the test db):</p>
  
  <pre><code>ALTER PROCEDURE dbo.crds_sp_GetFormAnswer 
      @FieldName varchar(max), 
      @TableName varchar(max), 
      @PrimaryKeyColumnName varchar(max), 
      @DataRecordId int
  AS
      DECLARE @sql nvarchar(max)
  
      SELECT @sql = 'SELECT ' + @FieldName +
                ' FROM ' + @TableName + 
                ' WHERE ' + @PrimaryKeyColumnName + '  =  ' + STR(@DataRecordId) + ';';
  
      EXEC sp_executesql @sql, 
              N'@FieldName varchar(max), @TableName varchar(max), 
                @PrimaryKeyColumnName varchar(max), @DataRecordId int', 
              @FieldName = @FieldName, @TableName = @TableName, 
              @PrimaryKeyColumnName = @PrimaryKeyColumnName, @DataRecordId = @DataRecordId;
  GO
  
  --test
  EXEC crds_sp_GetFormAnswer @FieldName = 'Submission_Data', 
                             @TableName = 'cr_Submissions', 
                             @PrimaryKeyColumnName = 'Submission_ID', 
                             @DataRecordId = 15;
  </code></pre>
  
  <p>In short, my end goal is to use dynamic SQL to populate a field. I have to use dynamic SQL because I am selecting from any number of tables. Business req was that use creates report to write to any table in the db if they have permissions (which is a separate topic).</p>
  
  <p><a href="https://i.stack.imgur.com/cJdTS.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/cJdTS.png" alt="enter image description here"></a></p>
  
  <p><strong>NOTE:</strong> I want to pass different data from each row into the stored procedure and have a different output for each row.</p>