title: Call dynamic SQL from function
body: <p>I am writing a function that returns a table. There are two parameters that are passed to the function and a query is built and executed and inserted into the returning table. However I am receiving this error. </p>
  
  <blockquote>
    <p>Only functions and some extended stored procedures can be executed from within a function. </p>
  </blockquote>
  
  <p>I would like to not use a stored procedure as this is a simple utility function. Does anyone know if this can be done. My function is coded below, it checks for dupes for a certain column within a certain table.</p>
  
  <pre><code>-- =============================================
  -- AUTHOR:      JON AIREY
  -- THIS FUNCTION WILL RETURN A COUNT OF HOW MANY
  -- TIMES A CERTAIN COLUMN VALUE APPEARS IN A 
  -- TABLE. THIS IS HELPFUL FOR FINDING DUPES.
  
  -- THIS FUNCTION WILL ACCEPT A COLUMN NAME, TABLE
  -- NAME (MUST INCLUDE SCHEMA), AND OPTIONAL
  -- DATABASE TO USE. RESULTS WILL BE RETURNED AS
  -- A TABLE.
  -- =============================================
  ALTER FUNCTION [dbo].[fn_FindDupe]
  (   
  -- Add the parameters for the function here
  @Column     VARCHAR(MAX), 
  @Table      VARCHAR(100),
  @Database   VARCHAR(100)    =   ''
  )
  RETURNS 
  @TempTable TABLE 
          ([Column] varchar(100)
          ,[Count] int)
  AS
  BEGIN
      DECLARE @SQL VARCHAR(MAX)
      SET @Table =    CASE
                          WHEN @Database = ''
                          THEN @Table
                          ELSE @Database + '.' + @Table
                      END
  
      SET @SQL =
  
      '   
          INSERT INTO @TempTable
  
          SELECT      ' + @Column + ' 
                      ,COUNT(' + @Column + ') AS CNT
          FROM        ' + @Table + '
          GROUP BY    ' + @Column + '
          ORDER BY    CNT DESC
      '
  
      EXEC SP_EXECUTESQL @SQL
  
  RETURN 
  END
  GO
  </code></pre>