[SQL]User-Defined Functions的參數預設值

2010-04-01

11174
0
SQL

摘要:[SQL]User-Defined Functions的參數預設值

今天學到痛苦又珍貴的一課，在此分享讓大家知道的複習一下、不知道的趁機學起來：

一般我們在寫Stored Procedures時，有的參數我們會給預設值，這樣一來呼叫端可以輸入或者省略這個參數，我最常用的時機是在要修改既有的Stored Procedures來增加參數，但又不希望造成現有的許多程式執行錯誤（最常發生是在不清楚前端有多少程式或是報表有呼叫此預存程序的情況下）。

原始程式

ALTER PROCEDURE [dbo].[Company_sel_GetMemberData]
@str_FirstName nvarchar(20),
@str_LastName nvarchar(10)
AS
BEGIN
.....
END

呼叫方式

Exec dbo.Company_sel_GetMemberData @str_FirstName = 'Kenny', @str_LastName = '%'

修改後(多增加一個參參數)

ALTER PROCEDURE [dbo].[Company_sel_GetMemberData]
@str_FirstName nvarchar(20),
@str_LastName nvarchar(10),
@int_Gender int = 1 --預設男性
AS
BEGIN
.....
END

呼叫方式

Exec dbo.Company_sel_GetMemberData @str_FirstName = 'Kenny', @str_LastName = '%'
或者是
Exec dbo.Company_sel_GetMemberData @str_FirstName = 'Kenny', @str_LastName = '%', @int_Gender = 1

也就是說我們可以在不中斷現有程式的運作下慢慢逐一修改(不論是前端還是後端)，對於整體的衝擊可以減到最低。

套用著同樣的道理一併修改了一堆User-Defined Functions (UDF)，範例如下

ALTER FUNCTION [dbo].[Company_fn_GetMemberBirthday]
(
    @str_MemberID int,
    @bln_ValidMember bit = 1   --預設找有效會員
)
RETURNS datetime
AS
BEGIN
....
END

結果發現使用原本的呼叫方式

Select dbo.Company_fn_GetMemberBirthday(2266)

馬上就掛了... 成所有的呼叫者全部呼叫失敗，一堆怨念頓時全部朝我飛來猛擊...

所有的呼叫者都必須改成下面的方式呼叫才不致發生錯誤

Select dbo.Company_fn_GetMemberBirthday(2266, 2) --給予實際數值
或是
Select dbo.Company_fn_GetMemberBirthday(2266, Default) --呼叫預設值

難道Stored procedure和UDF對於參數預設值的行為有所不同？MSDN給了我解答：

"A user-defined function takes zero or more input parameters and returns either a scalar value or a table. A function can have a maximum of 1024 input parameters. When a parameter of the function has a default value, the keyword DEFAULT must be specified when calling the function to get the default value. This behavior is different from parameters with default values in stored procedures in which omitting the parameter also implies the default value."

簡而言之，如果"有預設值的參數"沒有給予實際數值(亦即忽略輸入)的情況下，Stored Procedure會自動呼叫預設的值，但UDF不會。

回首頁