create table test(name char(200),dupt char(200),age datetime ) insert into testselect 'Davolio,Nancy','Sales Representative','1948-12-08 00:00:00.000' union all select 'Fuller,Andrew','Vice President, Sales','1952-02-19 00:00:00.000' union all select 'Leverling,Janet','Sales Representative','1963-08-30 00:00:00.000' union all select 'Peacock,Margaret','Sales Representative','1937-09-19 00:00:00.000' union all select 'Buchanan,Steven','Sales Manager Mr.','1955-03-04 00:00:00.000' create proc test_q @age int as declare @count int select @count=count(*) from test where year(getdate())-year(age)>@age if @count>0 print @count if @count=0 print 0go
select count(*) from table where datediff(y,出生日期,getdate())>50
create proc test_q @age int as declare @count int select @count=count(*) from table where datediff(y,出生日期,getdate())> @age return @count go
create proc test_q @age int as declare @count int select @count=count(*) from table where datediff(y,出生日期,getdate())> @age return @count goexec test_q 50
修改: create proc test_q @age int as declare @count int select @count=count(*) from test where datediff(y,age,getdate())>@age if @count>0 print @count if @count=0 print 0go
create proc xyear ( @age int,---设定年龄 @count int output ---返回年龄为@age的个数 ) as Begin ---假设表名是aaa select @count=count(*) from aaa where datediff(y,出生日期,getdate())>=@age End GO ---调用存储过程 declare @agecount int exec xyear 50,@agecount output print @agecount
给一个完整的例子给你吧:use tempdb GOcreate proc xyear ( @age int,---设定年龄 @count int output ---返回年龄为@age的个数 ) as Begin ---假设表名是aaa select @count=count(*) from aaa where datediff(yy,cast(出生日期 as datetime),getdate())>=@age End GOcreate table aaa(name char(200),dupt char(200),出生日期 datetime ) insert into aaaselect 'Davolio,Nancy','Sales Representative','1948-12-08 00:00:00.000' union all select 'Fuller,Andrew','Vice President, Sales','1952-02-19 00:00:00.000' union all select 'Leverling,Janet','Sales Representative','1963-08-30 00:00:00.000' union all select 'Peacock,Margaret','Sales Representative','1937-09-19 00:00:00.000' union all select 'Buchanan,Steven','Sales ManagerMr.','1955-03-04 00:00:00.000' declare @agecount int ---调用存储过程 exec xyear 50,@agecount output print '符合条件记录的条数:'+cast(@agecount as varchar) drop table aaa drop proc xyear ----------- 执行结果如下: (所影响的行数为 5 行)符合条件记录的条数:4
insert into testselect 'Davolio,Nancy','Sales Representative','1948-12-08 00:00:00.000' union all
select 'Fuller,Andrew','Vice President, Sales','1952-02-19 00:00:00.000' union all
select 'Leverling,Janet','Sales Representative','1963-08-30 00:00:00.000' union all
select 'Peacock,Margaret','Sales Representative','1937-09-19 00:00:00.000' union all
select 'Buchanan,Steven','Sales Manager Mr.','1955-03-04 00:00:00.000' create proc test_q @age int as
declare @count int
select @count=count(*)
from test
where year(getdate())-year(age)>@age
if @count>0
print @count
if @count=0
print 0go
@age int
as
declare @count int
select @count=count(*)
from table
where datediff(y,出生日期,getdate())> @age
return @count
go
@age int
as
declare @count int
select @count=count(*)
from table
where datediff(y,出生日期,getdate())> @age
return @count
goexec test_q 50
create proc test_q @age int as
declare @count int
select @count=count(*)
from test
where datediff(y,age,getdate())>@age
if @count>0
print @count
if @count=0
print 0go
(
@age int,---设定年龄
@count int output ---返回年龄为@age的个数
)
as
Begin
---假设表名是aaa
select @count=count(*) from aaa where datediff(y,出生日期,getdate())>=@age
End
GO
---调用存储过程
declare @agecount int
exec xyear 50,@agecount output
print @agecount
GOcreate proc xyear
(
@age int,---设定年龄
@count int output ---返回年龄为@age的个数
)
as
Begin
---假设表名是aaa
select @count=count(*) from aaa where datediff(yy,cast(出生日期 as datetime),getdate())>=@age
End
GOcreate table aaa(name char(200),dupt char(200),出生日期 datetime )
insert into aaaselect 'Davolio,Nancy','Sales Representative','1948-12-08 00:00:00.000' union all
select 'Fuller,Andrew','Vice President, Sales','1952-02-19 00:00:00.000' union all
select 'Leverling,Janet','Sales Representative','1963-08-30 00:00:00.000' union all
select 'Peacock,Margaret','Sales Representative','1937-09-19 00:00:00.000' union all
select 'Buchanan,Steven','Sales ManagerMr.','1955-03-04 00:00:00.000'
declare @agecount int
---调用存储过程
exec xyear 50,@agecount output
print '符合条件记录的条数:'+cast(@agecount as varchar)
drop table aaa
drop proc xyear
-----------
执行结果如下:
(所影响的行数为 5 行)符合条件记录的条数:4