请教一个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

回复赞