Transact-SQL中自定义函数的限制(转)

一直以为自定义函数只是比存储过程多了一个返回值而已，没有想到有这么多的限制，事情的起源都是因为下面这个简单的自定义函数引起的：

CREATE FUNCTION MyTestFunction
(
    @TestContent VARCHAR(20)
)
RETURNS VARCHAR(255)
AS
BEGIN
    INSERT INTO MyTestTable(Column1) VALUES(@TestContent)

    RETURN 'asdfasdf'
END
GO

当然业务逻辑不可能是这样的啦，只是举个例子，但是很简单的代码确报出以下错误：

消息 443，级别 16，状态 15，过程 MyTestFunction，第 9 行
在函数内的 'INSERT' 中对带副作用的或依赖于时间的运算符的使用无效。

我就纳闷了，凭啥函数里面不让我INSERT阿，后来又测试了一下，不光是INSERT，包括UPDATE和DELETE，也都不可以。找了一下MSDN，才明白了基本的道理，自定义函数和存储过程的定位是不一样的，自定义函数主要用于数据的计算，只能控制函数内部定义的局部变量，包括游标和表，都必须在函数内部定义才能使用，对外部数据库作出的任何永久性修改，都是禁止的。

具体来说，不能在函数中执行的操作包括：对数据库表的修改，对不在函数上的局部游标进行操作，发送电子邮件，尝试修改目录，以及生成返回至用户的结果集。

允许的操作也不少的，具体包含以下这些：
=> DECLARE 语句，该语句可用于定义函数局部的数据变量和游标。
=> 为函数局部对象的赋值，如使用 SET 为标量和表局部变量赋值。
=> 游标操作，该操作引用在函数中声明、打开、关闭和释放的局部游标。不允许使用 FETCH 语句将数据返回到客户端。仅允许使用 FETCH 语句通过 INTO 子句给局部变量赋值。
=> 除 TRY...CATCH 语句之外的控制流语句。
=> SELECT 语句，该语句包含具有为函数的局部变量赋值的表达式的选择列表。
=> INSERT、UPDATE 和 DELETE 语句，这些语句修改函数的局部表变量。
=> EXECUTE 语句，该语句调用扩展存储过程。

需要注意的是，系统函数中，有两个常用的，是不可以在函数中使用的，分别是newid和rand。

其实，如果又要执行以上限制的操作，又要返回值，自定义函数不是唯一的选择，可以在存储过程中增加OUTPUT参数，可以实现同样的效果。

posted @ 2007-05-16 17:33 明达 阅读(878) | 评论 (2) | 编辑

存储过程中OUTPUT类型参数的使用总结

作为演示，首先我们建立一个存储过程，当然这个存储过程会包含一个OUTPUT类型的参数：

CREATE PROCEDURE MyTest
    @MyVar varchar(25) OUTPUT
AS
BEGIN
    SELECT @MyVar = 'abc'
END
GO

这段代码很简单，重点在于OUTPUT类型的参数后面一定要有OUTPUT。

既然是输出的，当然要有个输出的地方，也就是为了运行这个存储过程，首先要先声明一个变量，调用的方法如下：

DECLARE @TestVar VARCHAR(25)

EXEC MyTest @TestVar OUTPUT

SELECT @TestVar

结果自然是abc，这里的关键就是传参的时候，一定要加OUTPUT，我老忘这个事情，呵呵。

上边是在SSMS中进行调用的，可是如果我们要是在ADO.NET中进行调用该怎么做呢，其实很简单的，当然额外要做的事情就是要加OUTPUT咯，具体参考以下代码：

public static string GetTestResult()
{
    string ret = string.Empty;

    using (SqlConnection sqlConnection = new SqlConnection(GetConnectionString()))
    {
        SqlCommand nonQueryCommand = new SqlCommand();
        nonQueryCommand.Connection = sqlConnection;
        nonQueryCommand.CommandText = "MyTest";
        nonQueryCommand.CommandType = CommandType.StoredProcedure;

        SqlParameter returnParameter = nonQueryCommand.Parameters.Add("@MyVar", SqlDbType.VarChar, 25);
        returnParameter.Direction = ParameterDirection.Output;

        nonQueryCommand.Connection.Open();
        nonQueryCommand.ExecuteNonQuery();

        ret = returnParameter.Value.ToString();
    }

    return ret;
}

关键的地方是要设置参数的Direction属性。

剩下的就是废话了，其实开始的时候是打算用SQL Server自带的自定义函数的，可是限制太多，比如在自定义函数中不可以使用Insert或者Update，比较恼火，呵呵。