SQL 自定义函数 

1 Create function 函数名（参数）
2 Returns 返回值数据类型
3 as
4 begin
5 SQL语句(必须有return 变量或值)
6 End
 1 --if exists(select * from sys.objects where name='test')
 2 --    drop function fun_Ribao_PLMSoftwarePassRate_ProductType
 3 /*
 4 用于取出软件合格率中的 产品类型
 5 传入文档分类ID，通过递归找到上级是 0开头的Name，
 6 返回 Name
 7 */
 8 create function fun_Ribao_PLMSoftwarePassRate_ProductType(@id nvarchar(50)) returns nvarchar(100) 
 9 as 
10 begin
11 declare @name nvarchar(100)
12 declare @oldName nvarchar(100)
13 select @name=TX.CMKINDNAME from DOC_017 AS TX where TX.CMKINDID = @id and TX.CMPARENTID is not null 
14 select @id = TX.CMPARENTID from DOC_017 AS TX where TX.CMKINDID = @id and TX.CMPARENTID is not null 
15  while @@ROWCOUNT > 0 
16     begin  
17         set @oldName=@name
18         select @name=TX.CMKINDNAME from DOC_017 AS TX where TX.CMKINDID = @id and TX.CMPARENTID is not null 
19          if(@name ='软件')  
20             begin
21              break
22             end
23          select @id = MXN.CMPARENTID
24          from DOC_017 AS MXN where MXN.CMKINDID 
25          = @id and MXN.CMPARENTID is not null 
26       end 
27  return @oldName
28 end
29 --调用
30 --SELECT dbo.fun_Ribao_PLMSoftwarePassRate_ProductType('47021259')
案列

 

posted @ 2019-09-06 15:38  Alex_Mercer  阅读(83)  评论(0)    收藏  举报