高手赐教!!! 写一个存储过程要求:系统自动产生将下一个月的表内的每天的空白记录,并且每条记录中有默认的日期。如:表是工作计划表 在1月26号自动产生2月的28天的每条空记录,并且每条记录都有2月相对应的日期想了很久都想不到,求高手赐教啊!!!数据库:SQL 2008 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 create proc sp_GetInfoasbegin declare @rq datetime set @rq=CONVERT(varchar(8),GETDATE(),120)+'01' select dateadd(dd,number,DATEADD(MM,1,@rq)) from master..spt_values where type='p' and number<day(dateadd(dd,-1,dateadd(mm,2,@rq)))endexec sp_GetInfo/*--------------------(无列名)2011-02-01 00:00:00.0002011-02-02 00:00:00.0002011-02-03 00:00:00.0002011-02-04 00:00:00.0002011-02-05 00:00:00.0002011-02-06 00:00:00.0002011-02-07 00:00:00.0002011-02-08 00:00:00.0002011-02-09 00:00:00.0002011-02-10 00:00:00.0002011-02-11 00:00:00.0002011-02-12 00:00:00.0002011-02-13 00:00:00.0002011-02-14 00:00:00.0002011-02-15 00:00:00.0002011-02-16 00:00:00.0002011-02-17 00:00:00.0002011-02-18 00:00:00.0002011-02-19 00:00:00.0002011-02-20 00:00:00.0002011-02-21 00:00:00.0002011-02-22 00:00:00.0002011-02-23 00:00:00.0002011-02-24 00:00:00.0002011-02-25 00:00:00.0002011-02-26 00:00:00.0002011-02-27 00:00:00.0002011-02-28 00:00:00.000*/ --sql 2000create procedure my_proc @date as datetimeasbegin declare @sdate datetime declare @edate datetime set @sdate = dateadd(mm,1,convert(varchar(7),@date,120) + '-01') set @edate = dateadd(mm,2,convert(varchar(7),@date,120) + '-01') - 1 select dateadd(dd,num,@sdate) dt from (select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a where dateadd(dd,num,@sdate)<=@edate order by dtendgoexec my_proc '2011-01-13'drop procedure my_proc/*dt ------------------------------------------------------ 2011-02-01 00:00:00.0002011-02-02 00:00:00.0002011-02-03 00:00:00.0002011-02-04 00:00:00.0002011-02-05 00:00:00.0002011-02-06 00:00:00.0002011-02-07 00:00:00.0002011-02-08 00:00:00.0002011-02-09 00:00:00.0002011-02-10 00:00:00.0002011-02-11 00:00:00.0002011-02-12 00:00:00.0002011-02-13 00:00:00.0002011-02-14 00:00:00.0002011-02-15 00:00:00.0002011-02-16 00:00:00.0002011-02-17 00:00:00.0002011-02-18 00:00:00.0002011-02-19 00:00:00.0002011-02-20 00:00:00.0002011-02-21 00:00:00.0002011-02-22 00:00:00.0002011-02-23 00:00:00.0002011-02-24 00:00:00.0002011-02-25 00:00:00.0002011-02-26 00:00:00.0002011-02-27 00:00:00.0002011-02-28 00:00:00.000(所影响的行数为 28 行)*/ --sql 2005create procedure my_proc @date as datetimeasbegin declare @startDate datetime declare @endDate datetime set @startDate = dateadd(mm,1,convert(varchar(7),@date,120) + '-01') set @endDate = dateadd(mm,2,convert(varchar(7),@date,120) + '-01') - 1 ;WITH tb AS ( SELECT @startDate AS 'date' UNION ALL SELECT DATEADD(DAY,1,date) FROM tb WHERE DATE<@endDate ) SELECT tb.date from tbendgoexec my_proc '2011-01-13'drop procedure my_proc/*date-----------------------2011-02-01 00:00:00.0002011-02-02 00:00:00.0002011-02-03 00:00:00.0002011-02-04 00:00:00.0002011-02-05 00:00:00.0002011-02-06 00:00:00.0002011-02-07 00:00:00.0002011-02-08 00:00:00.0002011-02-09 00:00:00.0002011-02-10 00:00:00.0002011-02-11 00:00:00.0002011-02-12 00:00:00.0002011-02-13 00:00:00.0002011-02-14 00:00:00.0002011-02-15 00:00:00.0002011-02-16 00:00:00.0002011-02-17 00:00:00.0002011-02-18 00:00:00.0002011-02-19 00:00:00.0002011-02-20 00:00:00.0002011-02-21 00:00:00.0002011-02-22 00:00:00.0002011-02-23 00:00:00.0002011-02-24 00:00:00.0002011-02-25 00:00:00.0002011-02-26 00:00:00.0002011-02-27 00:00:00.0002011-02-28 00:00:00.000(28 行受影响)*/ 连续号码拆分 sql语句中的case when用法? 如何在SQL SERVER中使用递归查询呢??求助 发布数据库和分发数据库在同一个server中,事务发布的数据库如何恢复? 急急急!!!sqlserver连接oracle时的错误,请高手指教!!! sql查询问题 请问:我要 create function ,将一个表名作为参数传入,应该怎么做? 请用一个sql语句得出结果,这是面试题目。 请教一个SQLserver的全文搜索问题? 请帮忙写出该条件的SQL语句 远程连接SQL Server2005失败 SqlServer怎么区分全角和半角?
as
begin
declare @rq datetime
set @rq=CONVERT(varchar(8),GETDATE(),120)+'01'
select dateadd(dd,number,DATEADD(MM,1,@rq))
from master..spt_values
where type='p' and number<day(dateadd(dd,-1,dateadd(mm,2,@rq)))
endexec sp_GetInfo
/*
--------------------
(无列名)
2011-02-01 00:00:00.000
2011-02-02 00:00:00.000
2011-02-03 00:00:00.000
2011-02-04 00:00:00.000
2011-02-05 00:00:00.000
2011-02-06 00:00:00.000
2011-02-07 00:00:00.000
2011-02-08 00:00:00.000
2011-02-09 00:00:00.000
2011-02-10 00:00:00.000
2011-02-11 00:00:00.000
2011-02-12 00:00:00.000
2011-02-13 00:00:00.000
2011-02-14 00:00:00.000
2011-02-15 00:00:00.000
2011-02-16 00:00:00.000
2011-02-17 00:00:00.000
2011-02-18 00:00:00.000
2011-02-19 00:00:00.000
2011-02-20 00:00:00.000
2011-02-21 00:00:00.000
2011-02-22 00:00:00.000
2011-02-23 00:00:00.000
2011-02-24 00:00:00.000
2011-02-25 00:00:00.000
2011-02-26 00:00:00.000
2011-02-27 00:00:00.000
2011-02-28 00:00:00.000
*/
create procedure my_proc @date as datetime
as
begin
declare @sdate datetime
declare @edate datetime
set @sdate = dateadd(mm,1,convert(varchar(7),@date,120) + '-01')
set @edate = dateadd(mm,2,convert(varchar(7),@date,120) + '-01') - 1
select
dateadd(dd,num,@sdate) dt
from
(select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a
where
dateadd(dd,num,@sdate)<=@edate
order by dt
end
goexec my_proc '2011-01-13'drop procedure my_proc/*
dt
------------------------------------------------------
2011-02-01 00:00:00.000
2011-02-02 00:00:00.000
2011-02-03 00:00:00.000
2011-02-04 00:00:00.000
2011-02-05 00:00:00.000
2011-02-06 00:00:00.000
2011-02-07 00:00:00.000
2011-02-08 00:00:00.000
2011-02-09 00:00:00.000
2011-02-10 00:00:00.000
2011-02-11 00:00:00.000
2011-02-12 00:00:00.000
2011-02-13 00:00:00.000
2011-02-14 00:00:00.000
2011-02-15 00:00:00.000
2011-02-16 00:00:00.000
2011-02-17 00:00:00.000
2011-02-18 00:00:00.000
2011-02-19 00:00:00.000
2011-02-20 00:00:00.000
2011-02-21 00:00:00.000
2011-02-22 00:00:00.000
2011-02-23 00:00:00.000
2011-02-24 00:00:00.000
2011-02-25 00:00:00.000
2011-02-26 00:00:00.000
2011-02-27 00:00:00.000
2011-02-28 00:00:00.000(所影响的行数为 28 行)*/
create procedure my_proc @date as datetime
as
begin
declare @startDate datetime
declare @endDate datetime
set @startDate = dateadd(mm,1,convert(varchar(7),@date,120) + '-01')
set @endDate = dateadd(mm,2,convert(varchar(7),@date,120) + '-01') - 1
;WITH tb AS (
SELECT @startDate AS 'date'
UNION ALL
SELECT DATEADD(DAY,1,date) FROM tb WHERE DATE<@endDate
)
SELECT tb.date from tb
end
goexec my_proc '2011-01-13'drop procedure my_proc/*
date
-----------------------
2011-02-01 00:00:00.000
2011-02-02 00:00:00.000
2011-02-03 00:00:00.000
2011-02-04 00:00:00.000
2011-02-05 00:00:00.000
2011-02-06 00:00:00.000
2011-02-07 00:00:00.000
2011-02-08 00:00:00.000
2011-02-09 00:00:00.000
2011-02-10 00:00:00.000
2011-02-11 00:00:00.000
2011-02-12 00:00:00.000
2011-02-13 00:00:00.000
2011-02-14 00:00:00.000
2011-02-15 00:00:00.000
2011-02-16 00:00:00.000
2011-02-17 00:00:00.000
2011-02-18 00:00:00.000
2011-02-19 00:00:00.000
2011-02-20 00:00:00.000
2011-02-21 00:00:00.000
2011-02-22 00:00:00.000
2011-02-23 00:00:00.000
2011-02-24 00:00:00.000
2011-02-25 00:00:00.000
2011-02-26 00:00:00.000
2011-02-27 00:00:00.000
2011-02-28 00:00:00.000(28 行受影响)*/