Question

sqlserver 2000 自定义函数

CREATE function dbo.GetPersonTable(@personID int, @needSelf int)
returns table as
return(
if @needSelf = 0

SELECT a.* from dbo.v_Person as a
inner join dbo.m_SysUser_Popedom as b on a.i_deptid=b.deptid and b.userid = @personID

else

SELECT a.* from dbo.v_Person as a inner join dbo.m_SysUser_Popedom as b on a.i_deptid=b.deptid and b.userid = @personID
union
select c.* from dbo.v_Person as c where c.personid = @personid
)

报if附近有语法错误 附：dbo.v_Person是视图

Answer 1

表值函数这样写（需要先定义一个表，然后向定义的表中插入记录即可）
CREATE function dbo.GetPersonTable(@personID int, @needSelf int)
returns @table table(fielda int,fieldb varchar(10))--定义返回表结构，要与你后面查询中字段一致
begin
if @needSelf = 0

insert into @table SELECT a.* from dbo.v_Person as a
inner join dbo.m_SysUser_Popedom as b on a.i_deptid=b.deptid and b.userid = @personID

else
insert into @table
SELECT a.* from dbo.v_Person as a inner join dbo.m_SysUser_Popedom as b on a.i_deptid=b.deptid and b.userid = @personID
union
select c.* from dbo.v_Person as c where c.personid = @personid
return
end
go