从函数调用动态 SQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9607935/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Call dynamic SQL from function
提问by JBone
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
回答by Michael Fredrickson
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.
这非常简单：您不能从用 T-SQL 编写的已用定义函数中使用动态 SQL。这是因为您不得在 UDF 中执行任何可能更改数据库状态的操作（因为 UDF 可能会作为查询的一部分被调用）。由于您可以从动态 SQL 中执行任何操作，包括更新，很明显为什么不允许使用动态 SQL。
...
在 SQL 2005 及更高版本中，您可以将函数实现为 CLR 函数。回想一下，来自 CLR 的所有数据访问都是动态 SQL。（您受到保护，因此如果您从您的函数中执行更新操作，您将被抓住。）不过有一句警告：来自标量 UDF 的数据访问通常会导致性能问题。
