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.
