User-Defined Functions vs. Stored Procedure 參數預設值

2017-05-23

3271
0

在系統已經運行一段時間後, 可能根據後續的需求會需要修改原先已寫好的相關Stored Procedure 或是 User-Defined Functions(UDF)

最常遇到的修改狀況是可能需要再多傳入參數值~

因這些寫好的SP 或是 UDF 可能已經在很多地方被使用~ 該怎麼處理, 才不會影響原先已運行好的程式?

首先想到的, 原先已寫好的程式, 對這些新加入的參數值, 採用預設值的方法~ 也就是不修改原先程式, 若可以不傳入新參數也可以正常使用為首要考量~

先來看看SP 怎麼傳參數值?

Stored Procedures 範例

ALTER PROCEDURE [dbo].[SP_Sample]
@Param1 nvarchar(10),
@Param2 nvarchar(10)
AS
BEGIN
.....
END

呼叫方式：使用參數名稱方式給參數值

Exec dbo.SP_Sample @Param1 = 'aa', @Param2= 'bb'

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

ALTER PROCEDURE [dbo].[SP_Sample]
@Param1 nvarchar(10),
@Param2 nvarchar(10),
@Param3 nvarchar(10) = 'cc' --新加入的參數, 並給定預設值
AS
BEGIN
.....
END

呼叫方式: 若不傳入@Param3則會採用預設值(cc), 或是傳入@Param並指定值

Exec dbo.SP_Sample @Param1 = 'aa', @Param2= 'bb'
Exec dbo.SP_Sample @Param1 = 'aa', @Param2= 'bb', @Param3 ='dd'

若是沒有傳入新的參數值,SP會採用預設值, 我們後端呼叫的程式也可以不用改很多, 只要針對需要特別指定值的地方再多傳入參數即可

但若是今天要新增參數值的是個User-Defined Functions呢?

事情可能就沒那麼簡單了~

照著SP的使用經驗, 在UDF裡新加參數後也給預設值, 範例如下:

ALTER FUNCTION [dbo].[UDF_Sample] 
(    
    @Param1 nvarhcar(10), 
    @Param2 bit = 1   --預設值
)
RETURNS bit
AS
BEGIN
....
END

呼叫方式若是只有傳入第1個參數值

Select dbo.UDF_Sample(abc)

就會有錯誤訊息: 提供給程序或函數 dbo.UDF_Sample 的引數數量不足。
若是想要用預設值, 那麼要怎麼呼叫呢?

Select dbo.UDF_Sample(abc, default)

但是這樣子, 所有引用到這個UDF的程式, 都必須要多傳入default 或是指定值~

因此, 使用UDF若要新加入參數, 需要配合修改的幅度是比較大的~

MSDN也有提到,UDF使用預設值時是與Stored Procedures不同的 :

"When a parameter of the function has a default value, the keyword DEFAULT must be specified when the function is called to retrieve the default value. This behavior is different from using parameters with default values in stored procedures in which omitting the parameter also implies the default value. "

參考網址:https://docs.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql

回首頁