编写函数,该函数在编号存在情况下返回员工信息,否则返回'该员工编号不存在'
这样写错误的,我不知道怎么写啊create function f5(@ygbh char(6))
returns table
as
BEGIN
if exists (select * from employees where employeeid=@ygbh)
select * from employees where employeeid=@ygbh
else
return select '该员工编号不存在'
END
这样写错误的,我不知道怎么写啊create function f5(@ygbh char(6))
returns table
as
BEGIN
if exists (select * from employees where employeeid=@ygbh)
select * from employees where employeeid=@ygbh
else
return select '该员工编号不存在'
END
print '该员工编号不存在'
returns table
as
BEGIN
return
if exists (select * from employees where employeeid=@ygbh)
select * from employees where employeeid=@ygbh
else
select '该员工编号不存在'
END
试试
的形式只能用return
后语句返回,不能加begin end
---------
返回的表列必须固定,不能返回不同结构的表.
建议改为
create function f5(@ygbh char(6))
returns table
as
return (select * from employees where employeeid=@ygbh)
如果返回空表再判断输出该员工编号不存在
create function f5(@ygbh char(6))
returns @r table(ID char(160))
as
BEGIN
if exists (select * from employees where employeeid=@ygbh)
insert @r select employeeid from employees where employeeid=@ygbh
else
insert @r select '该员工编号不存在'
return
ENDselect *from dbo.f5('a')
returns @r table(
ID char(160),
name char(20),
birthday datetime,
sex bit,
address char(50),
zip char(6),
phonenumber char(12),
email char(30),
departmentid char(3)
)
as
BEGIN
if exists (select * from employees where employeeid=@ygbh)
insert @r select * from employees where employeeid=@ygbh
else
insert @r(id) select '该员工编号不存在'
return
ENDselect * from dbo.f7('000001')