Call dynamic SQL from function

Question

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.

Only functions and some extended stored procedures can be executed from within a function.

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.

-- =============================================
-- 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

Solution

You can't use dynamic sql in a udf:

This very simple: you cannot use dynamic SQL from used-defined functions written in T-SQL. This is because you are not permitted do anything in a UDF that could change the database state (as the UDF may be invoked as part of a query). Since you can do anything from dynamic SQL, including updates, it is obvious why dynamic SQL is not permitted.

...

In SQL 2005 and later, you could implement your function as a CLR function. Recall that all data access from the CLR is dynamic SQL. (You are safe-guarded, so that if you perform an update operation from your function, you will get caught.) A word of warning though: data access from scalar UDFs can often give performance problems.

Comments

[JBone]

How can I make this a CLR function?

[Michael Fredrickson]

How to: Create and Run a CLR SQL Server User-Defined Function

[Frode Nilsen]

'Since you can do anything from dynamic SQL, including updates, it is obvious why dynamic SQL is not permitted.', that is correct. But at the same time you have to parse your dynamic sql with eg. sp_executesql(), so SQL Server have every possibility to catch data changing queries and let others pass. So is the real reason - they didn't bother to implement it?