从函数调用动态SQL

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

我不想使用存储过程,因为这是一个简单的效用函数.有人知道这是否可以完成.我的功能编码如下,它检查某个表中某列的重复.

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

解决方法

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.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!