set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author:马骥
-- Create date: 2009-6-15
-- Description: 获得驾驶员过生日的记录
-- =============================================
ALTER PROCEDURE [dbo].[pro_DriverBrith]
@getdays varchar(10) --天数,
AS
--判断临时表是否存在 如果存在删除
if object_id('tempdb..#temp_bsbrith') is not null
BEGIN
drop table #temp_bsbrith
END
BEGIN
--创建临时表
create table #temp_bsbrith(
--名字
[name] [varchar](20) null,
--出生日期
[Brith] [datetime] null,
--出生日期的月日
[BrithShort] [varchar](20) null,
--倒计时天数
[countDay] [varchar](20) null,
--性别
[sex] [varchar](20) null,
)
declare cur_Brith cursor for
select 身份证号 from dbo.ChauPhone
open cur_Brith --打开游标
declare @driNumber varchar(20)
declare @briths varchar(20) --出生日期
declare @birthShort varchar(20) --生日月日
declare @days varchar(10) --倒计时天数
FETCH NEXT from cur_Brith into @driNumber
WHILE @@FETCH_STATUS = 0
BEGIN
--判断身份证号码的长度
if (len(@driNumber)=18)
BEGIN
set @briths=substring(@driNumber,7,4)+'-'+substring(@driNumber,11,2)+'-'+substring(@driNumber,13,2)
set @birthShort=substring(@driNumber,11,2)+'-'+substring(@driNumber,13,2)
END
else if (len(@driNumber)=15)
BEGIN
set @briths='19'+substring(@driNumber,7,2)+'-'+substring(@driNumber,9,2)+'-'+substring(@driNumber,11,2)
set @birthShort=substring(@driNumber,9,2)+'-'+substring(@driNumber,11,2)
END
--判断生日是否在7天内 如果在添加到临时表中
set @days=datediff(day,getdate(),Convert(datetime,'2009-'+@birthShort)) --倒计时天数
--判断@getdays是否为0 如果为0向临时表中添加所有记录
if(@getdays=0)
BEGIN
insert into #temp_bsbrith select 驾驶员, @briths,@birthShort,@days+'天',驾驶员性别 from dbo.ChauPhone where 身份证号=@driNumber
END
else
BEGIN
if(datediff(day,getdate(),Convert(datetime,'2009-'+@birthShort))<=@getdays and datediff(day,getdate(),Convert(datetime,'2009-'+@birthShort))>=0)
BEGIN
insert into #temp_bsbrith select 驾驶员, @briths,@birthShort,@days+'天',驾驶员性别 from dbo.ChauPhone where 身份证号=@driNumber
END
END
FETCH NEXT from cur_Brith into @driNumber
END
close cur_Brith --关闭游标
DEALLOCATE cur_Brith --删除游标
END
select * from #temp_bsbrith order by BrithShort