sql server 自定义函数

自定义函数(User-Defined Function)有两种，一种是标量UDF(Scalar-valued Functions)和表值UDF(Table-valued

Functions)，前者只返回单个数据值，而后者则返回一个表

CREATE FUNCTIONfunction_name(@parameter_name parameter_data_type)

RETURNS date_type   --返回返回值的数据类型

[WITH ENCRYPTION]  --如果指定了encryption 则函数被加密

[AS]

BEGIN

 function_body --函数体(即 Transact-SQL 语句)

 RETURN  表达式;

END

在编写自定义函数时需要注意的：

标量函数：

1.      所有的入参前都必须加@

2.      create后的返回，单词是returns，而不是return

3.      returns后面的跟的不是变量，而是返回值的类型，如：int，char等。

4.      在begin/end语句块中，是return。

内联表格值函数：

1.      只能返回table，所以returns后面一定是TABLE

2.      AS后没有begin/end，只有一个return语句来返回特定的记录。

多语句表值函数：

1.      returns后面直接定义返回的表类型，首先是定义表名，表明前面要加@，然后是关键字TABLE，最后是表的结构。

2.      在begin/end语句块中，直接将需要返回的结果insert到returns定义的表中就可以了，在最后return时，会将结果返回。

3.      最后只需要return，return后面不跟任何变量。

例子：

ALTER function [dbo].[splitDt]

(

@SourceSqlvarchar(max),--传入参数

@StrSepratevarchar(6),

@BHvarchar(20)

)

returns @temp table(MCH varchar(100),BH varchar(20))---@ 表示局部变量。Returns返回值

as

begin

    declare@i int

    set@SourceSql = rtrim(ltrim(@SourceSql))--去除左右空格

    set@i = charindex(@StrSeprate,@SourceSql)--查找，数

    while@i >= 1---有逗号

    begin

        if len(left(@SourceSql,@i-1))>0--返回字符表达式中的字符数,值大于1个

        begin

            insert @temp values(left(@SourceSql,@i-1),@BH)

        end

        set @SourceSql=substring(@SourceSql,@i+len(@StrSeprate),len(@SourceSql)-@i)

        set @i=charindex(@StrSeprate,@SourceSql)

    end

    if@SourceSql <> ''---值是一个，没有逗号

        insert @temp values(@SourceSql,@BH)

    return

end

使用：

select mch,bh FROM splitDt(RTRIM('医疗器械第一类,中成药,化妆品,消毒品,其他,化学药制剂'),',','550640')

结果：

1