sql server用户自定义函数

1. 返回标量数据的函数
用户定义函数接受零个或更多的输入参数，并返回单值。一些用户定义函数返回单个的标量数据值，如 int、char 或 decimal 值。

例如，下面的语句创建一个返回 decimal 的简单函数：

CREATE FUNCTION CubicVolume
-- Input dimensions in centimeters.
   (@CubeLength decimal(4,1), @CubeWidth decimal(4,1),
    @CubeHeight decimal(4,1) )
RETURNS decimal(12,3) -- Cubic Centimeters.
AS
BEGIN
   RETURN ( @CubeLength * @CubeWidth * @CubeHeight )
END

注意：和存储过程有三个地方不同：
１.参数必须写在括号里面
２.参数后面有一条ｒｅｔｕｒｎ语句，　表示返回值的类型
３.函数体内需要有ｒｅｔｕｒｎ，返回结果。

２.　如何调用函数

dbo.functionName(Parameter1,...)

然后可以在允许整型表达式的任何地方（如表的计算列中）使用该函数：

CREATE TABLE Bricks
   (
    BrickPartNmbr   int PRIMARY KEY,
    BrickColor      nchar(20),
    BrickHeight     decimal(4,1),
    BrickLength     decimal(4,1),
    BrickWidth      decimal(4,1),
    BrickVolume AS
              (
               dbo.CubicVolume(BrickHeight,
                         BrickLength, BrickWidth)
              )
   )

这里的计算列还是第一次看到， 原来以为这个是更高级别的DataSet里面的东西。

3. 返回表变量的函数
在返回 table 的用户定义函数中：

RETURNS 子句为函数返回的表定义局部返回变量名。RETURNS 子句还定义表的格式。局部返回变量名的作用域位于函数内。


函数主体中的 Transact-SQL 语句生成行并将其插入 RETURNS 子句所定义的返回变量。


当执行 RETURN 语句时，插入变量的行以函数的表格格式输出形式返回。RETURN 语句不能有参数。
函数中返回 table 的 Transact-SQL 语句不能直接将结果集返回用户。函数返回用户的唯一信息是由该函数返回的 table。

下例在 Northwind 数据库中创建一个返回 table 的函数：

CREATE FUNCTION LargeOrderShippers ( @FreightParm money )
RETURNS @OrderShipperTab TABLE
   (
    ShipperID     int,
    ShipperName   nvarchar(80),
    OrderID       int,
    ShippedDate   datetime,
    Freight       money
   )
AS
BEGIN
   INSERT @OrderShipperTab
        SELECT S.ShipperID, S.CompanyName,
               O.OrderID, O.ShippedDate, O.Freight
        FROM Shippers AS S INNER JOIN Orders AS O
              ON S.ShipperID = O.ShipVia
        WHERE O.Freight > @FreightParm
   RETURN
END

在这个函数中，返回的本地变量名是 @OrderShipperTab。函数主体中的语句将行插入变量 @OrderShipperTab，以生成该函数返回的表结果。

下面的查询在其 FROM 子句中引用函数返回的表：

SELECT *
FROM LargeOrderShippers( $500 )

注意函数内不能使用临时表， 但可以使用表变量
函数头部return子句也是定义要返回的表变量， 但格式比较特殊。
在函数体内定义表变量的一般格式为：
declare @tableVar Table（
colName type，
。。。
）


临时表和表变量的区别：

临时表分为本地和全局两种，本地临时表的名称都是以“#”为前缀，只有在本地当前的用户连接中才是可见的，当用户从实例断开连接时被删除。全局临时表的名称都是以“##”为前缀，创建后对任何用户都是可见的，当所有引用该表的用户断开连接时被删除。

表变量创建的语法类似于临时表，区别就在于创建的时候，必须要为之命名。表变量是变量的一种，表变量也分为本地及全局的两种，本地表变量的名称都是以“@”为前缀，只有在本地当前的用户连接中才可以访问。全局的表变量的名称都是以“@@”为前缀，一般都是系统的全局变量，像我们常用到的，如 @@Error代表错误的号，@@RowCount代表影响的行数。

　比较临时表及表变量都可以通过SQL的选择、插入、更新及删除语句，它们的的不同主要体现在以下这些：

　　1)表变量是存储在内存中的，当用户在访问表变量的时候，SQL Server是不产生日志的，而在临时表中是产生日志的;

　　2)在表变量中，是不允许有非聚集索引的;

　　3)表变量是不允许有DEFAULT默认值，也不允许有约束;

　　4)临时表上的统计信息是健全而可靠的，但是表变量上的统计信息是不可靠的;

　　5)临时表中是有锁的机制，而表变量中就没有锁的机制。



对于函数中不能支持临时表是由于函数不能对函数作用域外部的资源状态造成永久性的更改，在SQLServer中也称为副作用 (sideeffect)。不过如果在函数中使用大型的临时结果集是不推荐的，因为如果将这样的函数放置到一个查询中会造成很明显的性能问题，因此这种情况一般都采用存储过程之类的批处理脚本。

　　对于动态脚本不支持表变量的原因是因为存储过程不接受表类型的参数。不过如果表变量的声明和赋值都在sp_executesql的参数中的话， sp_executesql就可以执行了，因为这个时候表变量就存在sp_executesql的stmt参数里面，不需要传入，例如下面的代码：(当然这样的实用性也就没有多少了)

　　DECLARE @m nvarchar(max)

　　SET @m = N"DECLARE @t TABLE (ID int);INSERT INTO @tVALUES(1);SELECT * FROM @t T"

　　EXEC sp_executesql @m