title: Writing a dynamic SQL function
body: <p>I am having a few problems trying to return a table from a SQL function, where the SQL to create the table is written dynamically.</p>
  
  <p>So far I have:</p>
  
  <pre><code>CREATE FUNCTION dbo.SEL_PCD
  (
      @COBDate AS DATETIME,
      @FileName AS VARCHAR(50),
      @PC AS VARCHAR(50),
      @MyList AS VARCHAR(max),
  
      DECLARE @SQL VARCHAR(max)
  
      SET @SQL = 'SELECT * FROM 
                      (SELECT tab1.TID FROM
                          (SELECT TID FROM dbo.SEL_RT('+@COBDate+','+@FileName+') WHERE BID IN ('+ @MyList +')) tab1
                      JOIN
                          (SELECT TID FROM CT WHERE (Col_Name LIKE %' + @PC + '% OR Bk LIKE %' + @PC + '%) AND FileName = ' + @FileName + ' AND COBDate = @COBDate) tab2
                      ON tab1.TID = tab2.TID) tab3
                  JOIN
                      (SELECT TID, Value FROM CR WHERE BID IN (' + @MyList + ') AND COBDate = ' + @COBDate + ' AND FileName = ' + @FileName + 'AND ScenID = 266) tab7
                  ON tab3.TID = tab7.TID'
  
  )
  RETURNS TABLE AS
  RETURN
  (
      EXEC sp_executesql @SQL
  )
  GO
  </code></pre>
  
  <p>I am getting errors declaring the SQL variable. Am I ok to return the table via the execute command?</p>