create function [ owner_name.] function_name

( { { @parameter_name scalar_parameter_data_type

[,…n] ] )

return scalar_return_data_type

[with < function_option> [, … n]]

[as]

begin

function_body

return scalar_expression

end

每個udf可以帶有0個到1024個參數，每個參數可以是除了timestamp、cursor、table 以外所有的數據類型；函數返回值的限制要更多一些，它不可以是text、ntext、image、timestamp、cursor和table。

函數體是udf的主要部分，它有兩個選項: encryotion和 schemabinding。

schemabinding是sql server 2000的新增功能，可以和視圖一同使用。該選項不允許刪除和修改被該函數引用的對象。這樣可以防止無效的函數和視圖對它們引用的對象進行結構上的修改。

大家會注意到函數體以begin開始，end結束。這一點不同于創建存儲過程、觸發器和視圖。當您忘了寫上begin/end時，系統會返回一個提示信息“incorrect syntax near ‘return’”。為什么不直接說少了begin/end，這有點讓人費解。

下面我用幾個例子來說明udf的應用。

greatest and least

　　為了區別于系統函數max和min，我給新函數命名為greatest和least，它們會從以參數形式輸入的兩個值中找出最大值和最小值。

case語句是兩個函數的核心：

case when value1 > value2 then value1 else value2 end

雖然函數很簡單，但用途是很廣的。

create function dbo.greatest

-- return the maximum of two parameters

(@val1 sql_variant,

@val2 sql_variant)

returns sql_variant

as

begin

return (case when @val1 > @val2 then @val1 else @val2 end)

end

go

―――――――――――――――――――――――――――――――

create function dbo.least

-- return the minimum of two parameters

( @val1 sql_variant,

@val2 sql_variant )

returns sql_variant

as

begin

return (case when @val1 < @val2 then @val1 else @val2 end)

end

go

大小寫轉換函數

該函數有兩個參數：@string和@capitalize_what。

依據 @capitalize_what的值，函數有不同的功能：

¨ @capitalize_what ＝ ‘string’“

函數將 @string的第一個非空字符轉換成大寫， 其余部分改為小寫。

¨ @capitalize_what ＝ ‘sentence’

函數將 @string中的每一句的首個非空字符轉換為大寫，句子其余部分轉換為小寫。斷句的依據是’.’、’!’、’?’

¨ @capitalize_what ＝ ‘word’

函數將 @string中的每個詞都轉換成首字符大寫，其余小寫的形式。

create function dbo.capitalize (

-- capitalize the first character of every word,

-- sentence, or the whole string. put the rest to lowercase.

@string varchar (8000),

@capitalize_what varchar (8) = ’string’

-- string: capitalize the first letter of the string

-- sentence: capitalize the first letter of every sentence.

-- delimiters: ./!/?

-- word: capitalize the first letter of every word.

-- delimiters: any characters other than letters and digits.

)

returns varchar(8000)

as

begin

declare @position smallint,

@char char(1),

@first_char char (1),

@word_start smallint

set @capitalize_what = lower( @capitalize_what )

set @word_start = 0

if @capitalize_what in (‘word’, ‘sentence’)

begin

set @position = datalength( @string )

while @position >= 0 begin

set @char = case @position

when 0 then ’.’

else upper( substring(

@string, @position,

1 ) )

end

if @char between ’a’ and ’z’

or @char between ’0’ and ’9’ begin

set @word_start = @position

set @first_char = upper( @char )

end

else begin

if @capitalize_what = ’word’

or @char in ( ’.’, ’!’, ’?’ ) begin

if @word_start > 0

and @first_char between ’a’

and ’z’

set @string = stuff(

@string, @word_start,

1, @first_char )

set @word_start = 0

end

end

set @position = @position - 1

end

end

else begin -- capitalize the first character

set @position = 0

while @position < datalength( @string )

begin

set @position = @position + 1

set @char = upper( substring( @string,

@position, 1 ) )

if @char between ’a’ and ’z’

or @char between ’0’ and ’9’ begin

set @string = stuff( @string,

@position, 1, @char )

set @position = 9999

end

end

end

return( @string )

end

go

小結

sql server 2000 的 udf的應用是很廣泛的，它會給編程人員帶來極大的便利。您可以建立自己的’system’ udf，存在master數據庫中，可以為任何數據庫進行調用。

udf也有不足，我們知道系統函數可以任意調有，不管您使用大寫、小寫或者大小寫混合。udf卻不行，它是大小寫敏感的。

在未來的版本中，我希望微軟為udf增加默認值的功能，以后我們可以這樣定義一個函數。

creat function dbo.test_default

（ @parm int = 0 ）

return int

as

begin

return ( @parm )

end

udf中諸如此類的小問題還有不少，希望udf的功能越來越強大，我們編程人員工作起來就會越來越輕松。