SQL语句用户自定义函数

 /*UDF概述：
  在其核心部分，UDF 既类似于视图，也类似于存储过程。像视图一样，UDF 可以返回一个行集，
  该行集可用于 JOIN 中。因此，当 UDF 返回一个行集并接受参数时，它像一个您可以联接到的
  存储过程、或者一个参数化的视图。
 
  有两种主要的 UDF 类型：返回标量值的 UDF 和返回表值的 UDF。
 
  标量UDF：意思是这样的函数和在C/C#等语言中定义的具有普通类型返回值如 int f();函数一样。
  但以下类型不能作为返回值：text、ntext、image、cursor、或 timestamp。这个类型除了SQL规
  定可以返回的类型外，由于SQL可以自定义类型，只要这些类型是基于标量类型（除text、ntext、
  image、cursor、或 timestamp外）也可以作为返回类型。
 
  表值 UDF:返回内联表值的 UDF 和返回多语句表值的 UDF。
  返回内敛表值的UDF不能在定义它将返回的表的SQL语句之外包含其他SQL逻辑。然而，它们比返回
  多语句表的 UDF 要容易创建，因为它们不必定义要返回的确切表结构。返回内联表的 UDF 从 SELECT
  语句本身推断行集的结构。注意：其中的SQL语句不能使用order by，除非和top一起使用。需要在SQL
  之外使用其他SQL逻辑，就必须将其转换成返回多语句表值的 UDF。
  返回多语句表的 UDF 显式定义要返回的表的结构。它通过在 RETURNS 子句中正确定义列名称和数据
  类型来做到这一点。因此，它会使用比返回内联表值的 UDF 稍多的代码来建立表结构。然而，与返回
  内联表值的 UDF 相比，它有几个优点，其中包括容纳更复杂的、更大量的 T-SQL 逻辑块的功能。顾名
  思义，返回多语句表值的 UDF 允许多个语句定义 UDF。因此，诸如流控制、分配、游标、SELECTS、
  INSERTS、UPDATES 和 DELETES 等语句都是允许的，并且都可以存在于单个 UDF 中。所以，与返回内
  联表的 UDF 相反，返回多语句表的 UDF 并不限定于单个 SELECT 语句，也不禁止对返回行集进行排序。
 
  调用方式：对象所有者.对象名进行调用e.g.:dbo.Calculation;当函数返回的是Scalar则dbo是必
  须的。返回值类型使用的是RETURNS
 
  函数嵌套：UDF 可以互相嵌套，只要其中的 UDF 是先创建的即可。使用嵌套函数的一个 catch 是
  非确定性内置函数（例如 getdate 函数），不能在另一个 UDF 内嵌套（否则会引发 SQL Server
  错误）。非确定性函数是用完全相同的参数调用多次时可能返回不同结果的函数。getdate 函数属
  于这一类，因为每次调用时，它会返回新的当前日期和时间。另一个常用的非确定性内置函数是
  NewID 函数。它也是非确定性的，因为它总是返回唯一的 GUID，所以 NewID 函数同样不允许在
   UDF 内嵌套。UDF也可以递归，在SQL2000中递归深度为32层。
*/

--Scalar UDFs
-- 计算两数字绝对差值
CREATE FUNCTION Calculation(@a DATETIME,@b DATETIME) RETURNS DATETIME
AS
BEGIN
 DECLARE @c DATETIME
 IF(@a<=@b)
 SET @c=@b-@a
 ELSE
    SET @c=@a-@b
   
 RETURN @c
END

SELECT  * FROM dbo.Employees
SELECT dbo.Employees.LastName,dbo.Employees.LastName,
       dbo.Calculation(dbo.Employees.BirthDate,dbo.Employees.HireDate)  AS cal
       FROM dbo.Employees

--格式化电话号码
CREATE FUNCTION FormatTelephoneNum(@phoneNum CHAR(10)) RETURNS VARCHAR(15)
AS
BEGIN
 DECLARE @pNum VARCHAR(15)
 IF LEN(@phoneNum)<10
  SET @pNum=@phoneNum
 ELSE
  SET @pNum='('+LEFT(@phoneNUM,3)+')'+SUBSTRING(@phoneNum,4,3)+'-'+RIGHT(@phoneNum,4)
 RETURN @pNum
END

SELECT dbo.FormatTelephoneNum('02345856681')

--表值 UDF;
--返回内敛表值
CREATE FUNCTION GetEmployeesCity(@city varchar(50)) RETURNS TABLE   
AS
RETURN (SELECT dbo.Employees.FirstName,dbo.Employees.LastName,dbo.Employees.City
        FROM dbo.Employees WHERE dbo.Employees.City=@city)
       
--多语句表值函数；注意调用时对象是表，所以要用select * from f(x)
CREATE FUNCTION GetEmployeesCityEx(@city varchar(50)) 
RETURNS @tmpTable TABLE
(
 FirstName varchar(20),
 LastName varchar(40),
 city varchar(120)
)
AS
BEGIN
 INSERT @tmpTable
 SELECT dbo.Employees.FirstName,dbo.Employees.LastName,dbo.Employees.City
 FROM dbo.Employees
 WHERE  dbo.Employees.City=@city
 ORDER BY dbo.Employees.LastName
 RETURN
END
       
SELECT * FROM dbo.GetEmployeesCityEx('Seattle')

CREATE FUNCTION GetEmployeesCityExx(@city varchar(15))
RETURNS @tmpTable TABLE
(
 FirstName varchar(20),
 LastName varchar(40),
 city varchar(120)
)
AS
BEGIN
 INSERT @tmpTable
 SELECT dbo.Employees.FirstName,dbo.Employees.LastName,dbo.Employees.City
 FROM dbo.Employees
 WHERE dbo.Employees.City=@city
 ORDER BY LastName
 
 IF NOT EXISTS(SELECT * FROM @tmpTable)
    INSERT @tmpTable(city) VALUES('no match')
 
 RETURN 
END
DROP FUNCTION GetEmployeesCityExx
SELECT * FROM dbo.GetEmployeesCityExx('Seattle')

--判断返回的结果是否为空  
IF NOT EXISTS (SELECT Name FROM syscolumns WHERE id=OBJECT_ID('dbo.yozhu')
    AND (autoval IS NOT NULL OR status=128))
BEGIN
    PRINT N'不存在'
END
ELSE
BEGIN
 PRINT N'存在'
END