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

[chenwei316]

我想写一个函数,就是返回一个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


请大家帮忙看看,在线等待

[chenwei316]

感谢,各位的支持,谢谢大家!

[teaism]

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

[teaism]

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]

不好意思,上一个错了:

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

[zjcxc]

return table 是返回一个select 语句的执行结果.它的函数体只有一句.

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



而如果是楼主的这种情况,就要明确指定要返回的表的结构.

[zjcxc]

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

[LoveSQL]

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