3、创建和调用存储过程
(1)要求:编写一个存储过程,实现根据员工的出生日期计算出其实际年龄。调用该存储过程计算工号为“0000010”的员工年龄。
请问这个问题怎么解决啊?
person(pno char(8),birthday datetime(),pname char(10),sex char(2))
(1)要求:编写一个存储过程,实现根据员工的出生日期计算出其实际年龄。调用该存储过程计算工号为“0000010”的员工年龄。
请问这个问题怎么解决啊?
person(pno char(8),birthday datetime(),pname char(10),sex char(2))
as
begin
select @cnt = datediff(yy , birthday , getdate()) from person where pno = @pno
end
go
则如果是2010-10-30日的,则为10岁,如果是2010-10-29,则算11岁.
create procedure my_proc @pno varchar(8) , @cnt int OUTPUT
as
begin
select @cnt = (case when right(convert(varchar(10),birthday,120),5) > right(convert(varchar(10),getdate(),120),5) then
datediff(yy , birthday , getdate()) - 1
else datediff(yy , birthday , getdate())
end)
from person where pno = @pno
end
go
@Pno char(8)
)
as
select case when dateadd(y,datediff(y,Birthday,getdate()),Birthday) >Birthday then datediff(y,Birthday,getdate())-1
else datediff(y,Birthday,getdate()) end as Age
from Person where Pno=@Pno
create procedure sp_getpage;3
(@pno char(8),@age int output)
as
select birthday age from person where pno=@pno and @age=2011-year(birthday)
declare @page int
exec sp_getpage;3 @pno='000010'
print @page
insert into person select '00000001','1990-01-25','aaa','no'
go
create procedure getage
(@pno varchar(8))
as
begin
select year(getdate())-year(birthday) from person where pno = @pno
end
go
exec getage '00000001'
/*
-----------
21(1 行受影响)*/
insert into person select '00000001','1990-01-25','aaa','no'
go
create procedure getage
(@pno varchar(8),@age int output)
as
begin
select @age=year(getdate())-year(birthday) from person where pno = @pno
end
go
declare @age int
exec getage '00000001',@age output
select @age
/*
-----------
21(1 行受影响)*/
go
drop table person
drop proc getage