请教一个SQL自定义函数的问题(在线等待)

chenwei316 2003-11-13 10:32:02
我想写一个函数,就是返回一个Table,其中要用到IF语句,出现问题,总是告诉我BEGIN处出错,和"标量值的函数中的 RETURN 语句必须含有参数"。请问是什么意思,另外我写了两个正确的但是不同内容,一个是返回VarChar(32)的其中用到了If...Else没有问题,另外一个是返回table的也没有问题,但是把它们合并后就不行了,现将3个SQL一并贴出请大家看看!
1.正确使用IF...ELSE的

ALTER function GetAllNextLevelDepID1 (@DepId varchar(32),@iType int)
RETURNS varchar(32)
as
begin
if @iType=0

RETURN
'0'

else
if @iType=1
return '1'
return '2'
end


2.正确使用返回Table的


ALTER function GetAllNextLevelDepID (@DepId varchar(32),@iType int)
RETURNS TABLE
as


RETURN
SELECT DISTINCT Dep.ID
FROM Dep
INNER JOIN Dep d ON (Dep.PathID LIKE d.PathID +'%' AND d.PathID<>Dep.PathID) OR d.ID=Dep.ID
WHERE d.ID=@DepId

3.错误的且同时使用IF...ELSE也是用返回Table

create function GetAllNextLevelDepID2 (@DepId varchar(32),@iType int)
RETURNS TABLE
as
begin
if @iType=0
begin
RETURN
SELECT DISTINCT Dep.ID
FROM Dep
INNER JOIN Dep d ON (Dep.PathID LIKE d.PathID +'%' AND d.PathID<>Dep.PathID) OR d.ID=Dep.ID
WHERE d.ID=@DepId
end
RETURN
SELECT DISTINCT Dep.ID
FROM Dep
INNER JOIN Dep d ON (Dep.PathID LIKE d.PathID +'%' AND d.PathID<>Dep.PathID)
WHERE d.ID=@DepId
end


请大家帮忙看看,在线等待
...全文
18 点赞 收藏 7
写回复
7 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
LoveSQL 2003-11-13
alter function GetAllNextLevelDepID2 (@DepId varchar(32),@iType int)
RETURNS @re TABLE(id varchar(32))
as
begin
if @iType=0
insert into @re
SELECT DISTINCT Dep.ID
FROM Dep
INNER JOIN Dep d ON (Dep.PathID LIKE d.PathID +'%' AND d.PathID<>Dep.PathID) OR d.ID=Dep.ID
WHERE d.ID=@DepId
else
insert into @re SELECT DISTINCT Dep.ID
FROM Dep
INNER JOIN Dep d ON (Dep.PathID LIKE d.PathID +'%' AND d.PathID<>Dep.PathID)
WHERE d.ID=@DepId
return
end
回复
zjcxc 2003-11-13
create function GetAllNextLevelDepID2 (@DepId varchar(32),@iType int)
RETURNS @re TABLE(id varchar(32))
as
begin
if @iType=0
insert into @re
SELECT DISTINCT Dep.ID
FROM Dep
INNER JOIN Dep d ON (Dep.PathID LIKE d.PathID +'%' AND d.PathID<>Dep.PathID) OR d.ID=Dep.ID
WHERE d.ID=@DepId
else
insert into @re SELECT DISTINCT Dep.ID
FROM Dep
INNER JOIN Dep d ON (Dep.PathID LIKE d.PathID +'%' AND d.PathID<>Dep.PathID)
WHERE d.ID=@DepId
return
end
回复
zjcxc 2003-11-13
return table 是返回一个select 语句的执行结果.它的函数体只有一句.

create fucntion 函数名(参数)
returns table
return(select 语句)



而如果是楼主的这种情况,就要明确指定要返回的表的结构.
回复
teaism 2003-11-13
不好意思,上一个错了:

create function GetAllNextLevelDepID2 (@DepId varchar(32),@iType int)
RETURNS @a TABLE (depid varchar(20))
as
begin
if @iType=0
begin
insert @a
SELECT DISTINCT Dep.ID
FROM Dep
INNER JOIN Dep d ON (Dep.PathID LIKE d.PathID +'%' AND d.PathID<>Dep.PathID) OR d.ID=Dep.ID
WHERE d.ID=@DepId
end
else
begin
insert @a
SELECT DISTINCT Dep.ID
FROM Dep
INNER JOIN Dep d ON (Dep.PathID LIKE d.PathID +'%' AND d.PathID<>Dep.PathID)
WHERE d.ID=@DepId
end
return @a
end
回复
teaism 2003-11-13
create function GetAllNextLevelDepID2 (@DepId varchar(32),@iType int)
RETURNS TABLE @a(depid varchar(20))
as
begin
if @iType=0
begin
insert @a
SELECT DISTINCT Dep.ID
FROM Dep
INNER JOIN Dep d ON (Dep.PathID LIKE d.PathID +'%' AND d.PathID<>Dep.PathID) OR d.ID=Dep.ID
WHERE d.ID=@DepId
end
else
begin
insert @a
SELECT DISTINCT Dep.ID
FROM Dep
INNER JOIN Dep d ON (Dep.PathID LIKE d.PathID +'%' AND d.PathID<>Dep.PathID)
WHERE d.ID=@DepId
end
return @a
end
回复
teaism 2003-11-13
create function GetAllNextLevelDepID2 (@DepId varchar(32),@iType int)
RETURNS TABLE
as
begin
if @iType=0
begin
RETURN
SELECT DISTINCT Dep.ID
FROM Dep
INNER JOIN Dep d ON (Dep.PathID LIKE d.PathID +'%' AND d.PathID<>Dep.PathID) OR d.ID=Dep.ID
WHERE d.ID=@DepId
end
else
begin
RETURN
SELECT DISTINCT Dep.ID
FROM Dep
INNER JOIN Dep d ON (Dep.PathID LIKE d.PathID +'%' AND d.PathID<>Dep.PathID)
WHERE d.ID=@DepId
end
end
回复
chenwei316 2003-11-13
感谢,各位的支持,谢谢大家!
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2003-11-13 10:32
社区公告
暂无公告