SQLServer自定義函數

在SQLServer中不僅可以使用系統函數（如：聚合函數，字符串函數，時間日期函數等）還可以根據需要自定義函數。

自定義函數分為標量值函數和表值函數。

其中，標量值函數用于返回單個值，而表值函數用于返回一個結果集。

函數參數

參數可以是常量、表中的某個列、表達式或其他類型的值。在函數中有三種類型的參數。

1、輸入：指必須輸入一個值。

2、可選值：在執行該參數時，可以選擇不輸入參數。

3、默認值：函數中默認有值存在，調用時可以不指定該值。

創建標量值函數

語法：

Create function 函數名（參數）Returns 返回值數據類型[with {Encryption | Schemabinding }][as]begin　　SQL語句(必須有return 變量或值)EndSchemabinding :將函數綁定到它引用的對象上（注：函數一旦綁定，則不能刪除、修改，除非刪除綁定）

例子：

drop function dbo.input  --刪除函數gocreate function dbo.input --定義函數 架構.方法名(@num1 int,   --輸入參數@num2 int = null,  --可選參數@oper varchar = '+' --默認參數)returns intasbegin    declare @sum int    if(@oper='+')    begin        set @sum = @num1 + @num2    end    else    begin        set @sum = 0    end    return @sumendgoselect dbo.input(1,null,default)  --參1必填，參2可選，參3默認select dbo.input(1,2,default) --輸出3select dbo.input(1,2,'*') --輸出0 *沒判斷

自定義函數可以將值放在局部變量中，用set select exec賦值

declare @number intselect @number = dbo.input(1,2,default)PRint @numberdeclare @set intset @set = dbo.input(1,2,default)print @setdeclare @exec intexec @exec = dbo.input 1,2,'+'print @exec

在查詢中引用函數

create table test(    id int identity(1,1),    name varchar(10),    birthDay datetime)insert into test values('張三','1998-02-01'),('李四','1981-10-1'),('王五','1985-5-2')select * from test --測試信息

創建函數并執行后

create function dbo.getAge(@birthDay datetime)returns intasbegindeclare @age intset @age = datediff(yy,@birthDay,getdate())return @ageendselect name as 姓名,dbo.getAge(birthDay) as 年齡 from test

注意：標量值函數不可以返回文本（text、ntext）、圖像、游標或時間戳類型的數據，并且不能用來修改數據庫狀態。

在select語句中使用函數可能會帶來負面影響，因為每返回一行都會調用函數一次。所以在返回大型數據集時應該格外避免使用復雜的函數。

表值函數

表值函數包含兩種類型：內聯函數和多語句函數。

內聯表值函數僅返回一個結果集，而多語句函數可以在函數體中包含一些控制邏輯。

1、內聯表值函數

語法：

create function 函數名（參數）returns table[with {Encryption | Schemabinding }]asreturn(一條SQL語句)

例子：

--創建create function getDetails(@id int)returns tableasreturn(select * from student where id = @id) --執行一條語句后返回--調用select * from dbo.getDetails(10)

2、多語句函數

多語句函數可以通過多條語句來創建臨時表，具體需要哪些字段，以及符合哪些要求的數據被添加到臨時表中等。

語法：

create function 函數名（參數）returns 表格變量名 table (表格變量定義)[with {Encryption | Schemabinding }]asbegin    SQL語句end 

例子：

create function dbo.Test()returns @temp table (    name varchar(20),    sex char(2),    age int)asbegininsert into @temp (name,sex,age) values ('多語句','嘛',18)insert into @temp (name,sex,age) select name,sex,age from student where age > 18return end

可以看出，多語句函數返回結果是定義好表結構的虛擬表，最后有一個return用來告訴sql多語句已經執行完畢。不寫會返回不了。