sqlserver创建函数的实例(create function)

发布时间：2020-12-12 15:07:37 所属栏目：MsSql教程来源：网络整理

导读：CREATE?FUNCTION? 名称(@ID VARCHAR(10)) RETURNS varchar(8000) AS BEGIN ?declare @i int,@ret varchar(8000) ??? declare @t table(ID VARCHAR(10),PID VARCHAR(10),Level INT) ??? set @i = 1 ??? insert into @t select CategoryID,ParentCategoryID,@i

CREATE?FUNCTION? 名称(@ID VARCHAR(10))
RETURNS varchar(8000)
AS
BEGIN
?declare @i int,@ret varchar(8000)
??? declare @t table(ID VARCHAR(10),PID VARCHAR(10),Level INT)
??? set @i = 1
??? insert into @t select CategoryID,ParentCategoryID,@i from Category where CategoryID = @ID
???
??? while @@rowcount<>0
??? begin
??????? set @i = @i + 1
???????
??????? insert into @t
??????? select
??????????? a.CategoryID,a.ParentCategoryID,@i
??????? from
??????????? Category a,@t b
??????? where
??????????? a.CategoryID=b.PID and b.Level = @i-1
??? end
???
??? select @ret = isnull(@ret,'')+ID+',' from @t order by Level
???
??? return @ret

END?

create?? function?? f_qry2(??
@xtype?? nvarchar(2)??
)returns?? @re?? table(id?? int,name?? sysname)??
as??
begin??
insert?? @re?? select?? id,name?? from?? sysobjects?? where?? xtype=@xtype??
return??
end??
go??
???
???
--调用??
select?? *?? from?? f_qry2('U')??
go
drop function f_qry2

?? --1.直接从select中返回??
create?? function?? f_qry1(??
@xtype?? nvarchar(2)??
)?? returns?? table??
as??
return(select?? *?? from?? sysobjects?? where?? xtype=@xtype)?? go?? ??? --调用?? select?? *?? from?? f_qry1('U')?? go?? drop function f_qry1

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!