用存储过程构造一个虚拟日期表发现的趣事

 

USE [SQL_Test]
GO
/****** Object:  StoredProcedure [dbo].[ProcReturnDaysTable]    Script Date: 08/05/2010 22:45:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ProcReturnDaysTable]
    -- Add the parameters for the stored procedure here
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    declare @ints int
    set @ints=2

    declare @sqlQuery varchar(5000)
    set @sqlQuery='select '''+CONVERT(varchar,YEAR(GETDATE()))+'-'+CONVERT(varchar,MONTH(GETDATE()))+'-1'' as timeing,1 timeorder '

    declare @sqlString varchar(5000)
    declare @months int
    set @months=dbo.FuncReturnDaysByYearAndMonth(YEAR(GETDATE()),MONTH(GETDATE()))--标量函数：获得当月天数
    declare @texts varchar(50)
    declare @times datetime
    declare @results varchar(2000)
    while(@ints<=@months)
    begin
        set @texts=(CONVERT(varchar,YEAR(getdate()))+'-'+CONVERT(varchar,Month(getdate()))+'-'+CONVERT(varchar,@ints))
        set @times=CONVERT(datetime,@texts)
        set @sqlstring='union select '''+@texts+''','+CONVERT(varchar,@ints)+' '
        set @sqlQuery=@sqlQuery+@sqlstring
        set @ints+=1
    end
    set @results='select * from('+@sqlQuery+') as tt order by timeorder asc'
END
exec (@results)

 

 

获得当月天数的标量函数

 

USE [SQL_Test]
GO
/****** Object:  UserDefinedFunction [dbo].[FuncReturnDaysByYearAndMonth]    Script Date: 08/05/2010 23:56:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[FuncReturnDaysByYearAndMonth]
(
    @GetYear int,
    @GetMonth int
)
RETURNS int
AS
BEGIN
    declare @resultDays int
    declare   @year   char(4),   @month   char(2) 
    select   @year=@GetYear,   @month=@GetMonth
    declare   @begDate   datetime,   @endDate   datetime 
    select   @begDate=@year+ '- '+@month+ '-01 ' 
    set @resultDays= datediff(day,   @begDate,   dateadd(month,   1,   @begDate))
    return @resultDays
END

 

 由于写给朋友的固过程比较详细 最后exec dbo.ProcReturnDaysTable可得出正确结果

 在其中遇到诡异SQL如下

 

select 2010-8-1 as timeing,1 timeorder union select 2010-8-2,2 union select 2010-8-3,3 

 

执行之后得到结果为：

 感到有点诡异，这让我想起来了以前Js日历的那种年份Bug