sqlserver 自定义函数的使用

用户自定义函数

查看当前数据库所有函数

–查询所有已创建函数
select definition,* from sys.sql_modules m join sys.objects o on m.object_id = o.object_id
and type in(‘fn’, ‘if’, ‘tf’);

创建函数

if (object_id(‘fun_add’, ‘fn’) is not null)
drop function fun_add
go
create function fun_add(@num1 int, @num2 int)
returns int
with execute as caller
as
begin
declare @result int;
if (@num1 is null)
set @num1 = 0;
if (@num2 is null)
set @num2 = 0;
set @result = @num1 + @num2;
return @result;
end
go
调用函数
select dbo.fun_add(id, age) from student;

–自定义函数,字符串连接
if (object_id(‘fun_append’, ‘fn’) is not null)
drop function fun_append
go
create function fun_append(@args nvarchar(1024), @args2 nvarchar(1024))
returns nvarchar(2048)
as
begin
return @args + @args2;
end
go

select dbo.fun_append(name, ‘abc’) from student;

修改函数

alter function fun_append(@args nvarchar(1024), @args2 nvarchar(1024))
returns nvarchar(1024)
as
begin
declare @result varchar(1024);
–coalesce返回第一个不为null的值
set @args = coalesce(@args, ‘’);
set @args2 = coalesce(@args2, ‘’);;
set @result = @args + @args2;
return @result;
end
go

select dbo.fun_append(name, ‘#abc’) from student;

返回table类型函数

–返回table对象函数
select name, object_id, type from sys.objects where type in (‘fn’, ‘if’, ‘tf’) or type like ‘%f%’;

if (exists (select * from sys.objects where type in (‘fn’, ‘if’, ‘tf’) and name = ‘fun_find_stuRecord’))
drop function fun_find_stuRecord
go
create function fun_find_stuRecord(@id int)
returns table
as
return (select * from student where id = @id);
go

select * from dbo.fun_find_stuRecord(2);

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 代码科技 设计师:Amelia_0503 返回首页