sqlserver 自定义函数的使用

admin管理员组

文章数量:1794759

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);

本文标签： 自定义函数sqlserver