sql自定义函数

两点要注意：

1：返回类型是returns 不是return

2：返回类型是table 还是varchar 还是table col 要注意使用的位置。

create function selectByonid(@oneid int)
returns table
as
return
(
select * from t_class where oneid=@oneid
)

select * from dbo.selectByonid(44)

输出：

-----------------------------------------------------

classname        sort        oneid       twoid       id         
----------------------- ----------- ----------- ----------- -----------
单位介绍                                                                                                 NULL        44          0           1
NULL                                                                                                 NULL        44          1           10
NULL                                                                                                 NULL        44          2           11
NULL                                                                                                 NULL        44          3           12
NULL                                                                                                 NULL        44          4           13
NULL                                                                                                 NULL        44          5           14
NULL                                                                                                 NULL        44          6           15
NULL                                                                                                 NULL        44          7           16

（所影响的行数为 8 行）

 

============================================

返回类型为 varchar

create function ReplaceNullToStr3(@str varchar(100))
returns varchar(100) --切记 不写100的话 只返回第一个字母
as
begin
if @str is null or @str=''
set @str='aaaa'
else
set @str='ddd'
return @str
end

update t_class set classname=dbo.ReplaceNullToStr3(classname)

select * from t_class

 

输出

--------------------------------------------------

classname                                                                                            sort        oneid       twoid       id         
---------------------------------------------------------------------------------------------------- ----------- ----------- ----------- -----------
ddd                                                                                                  NULL        44          0           1
ddd                                                                                                  NULL        45          0           2
ddd                                                                                                  NULL        46          0           3
ddd                                                                                                  NULL        47          0           4
ddd                                                                                                  NULL        48          0           5
ddd                                                                                                  NULL        49          0           6
ddd                                                                                                  NULL        50          0           7
ddd                                                                                                  NULL        51          0           8
ddd                                                                                                  NULL        52          0           9
ddd                                                                                                  NULL        44          1           10
ddd                                                                                                  NULL        44          2           11
ddd                                                                                                  NULL        44          3           12
ddd                                                                                                  NULL        44          4           13
ddd                                                                                                  NULL        44          5           14
ddd                                                                                                  NULL        44          6           15
ddd                                                                                                  NULL        44          7           16

（所影响的行数为 16 行）