Useful User-Defined Functions for SQL Server

0 comments
在資料庫設計中,我們常會應用預儲程序(Stored Procedure)來對資料進行 CRUD 操作。除此之外,你也可以撰寫自訂函數來應付經常性的資料存取作業或是複雜的計算等等。在這裡,我羅列幾個我在過去開發商務系統中常會應用到的自訂函數。

fn_GetNextBusinessDay
指定起始日期,並傳回最近一個營業日期。
CREATE FUNCTION dbo.fn_GetNextBusinessDay(@StartingDate datetime)
RETURNS datetime
AS
BEGIN
DECLARE @NextBusinessDay datetime

IF DATEPART(dw, @StartingDate) = (7 - @@DATEFIRST + FLOOR(@@DATEFIRST / 7) * 7) --Saturday
SET @NextBusinessDay = DATEADD(d, 2, @StartingDate)
ELSE IF DATEPART(dw, @StartingDate) = (8 - @@DATEFIRST) --Sunday
SET @NextBusinessDay = DATEADD(d, 1, @StartingDate)
ELSE
SET @NextBusinessDay = @StartingDate

RETURN @NextBusinessDay
END

fn_GetTax
指定銷售額及稅率,並傳回應付的營業稅額。
CREATE FUNCTION dbo.fn_GetTax(@Amount int, @TaxRate real)
RETURNS real
AS
BEGIN
DECLARE @UntaxedPrice int, @Tax int
SET @UntaxedPrice = ROUND(CAST(@Amount AS real) / (1 + @TaxRate), 0)
SET @Tax = @Amount - @UntaxedPrice

RETURN @Tax
END

fn_FormatPercent
指定浮點數資料,並傳回百分比的表示式。
CREATE FUNCTION dbo.fn_FormatPercent(@InputNumber float)
RETURNS varchar(20)
AS
BEGIN
RETURN CAST(CAST(@InputNumber * 100 AS numeric(10, 0)) AS varchar(20)) + '%'
END

fn_PadLeft
將輸入字串靠右對齊,以特定的字元在左側填補至指定的總長度。
CREATE FUNCTION dbo.fn_PadLeft(@InputString varchar(1024), @PaddingChar char(1), @FieldLength int)
RETURNS varchar(1024)
AS
BEGIN
DECLARE @PaddingString varchar(1024)
IF @FieldLength > 0
SET @PaddingString = RIGHT(REPLICATE(@PaddingChar, @FieldLength) + @InputString, @FieldLength)
ELSE
SET @PaddingString = @InputString

RETURN @PaddingString
END

繼續閱讀...