用Procedure取得全月的日期

方法1：

CREATE Procedure xl_ShinchokuWeek
	@tyosaDT varchar(7)
As

	DECLARE @tblMonthTyosa TABLE(
		[tyosaDT][varchar](14),
		[cntTyosaJishi][int] ,
		[cntTyosain][int],
		[cntTensaku1][int],
		[cntTensaku2][int],
		[cntCount][int],	--データの順番   
		[cntWeek][int],		--週目
		[cntGokei][int]		--合計フラグ ０：普通の時間、１：週間合計、２：月間合計
	) 

	DECLARE	@datefirst	datetime   
	DECLARE	@datelast	datetime
	DECLARE	@dateCnt	datetime
	DECLARE	@count		int	
	DECLARE	@week		int	
	DECLARE	@group		int	--計算用
    	
	Set @count = 1
	Set @week = 1
	Set @group = 1

	/*開始時間*/
	Set @datefirst = cast(@tyosaDT + '/01' as datetime)
    
	while datepart(dw,@datefirst) <> 2
		Begin 
			Set @datefirst = datediff(day,1,@datefirst)
		End

	/*結束時間*/
	Set @datelast = cast(datediff(d,1,dateadd(m,1,cast(@tyosaDT + '/01' as datetime))) as datetime)
    
	while datepart(dw,@datelast) <> 1
		Begin 
			Set @datelast = dateadd(day,1,@datelast)
		End
		
	/*時間*/
	Set @dateCnt = @datefirst

	WHILE @dateCnt <= @datelast
		Begin     
			--普通のデータ
			Insert into @tblMonthTyosa(tyosaDT, cntTyosaJishi, cntTyosain, cntTensaku1, cntTensaku2, cntCount, cntWeek, cntGokei)
			VALUES(substring(CONVERT(char(10),@dateCnt,111),1,10) + CASE datepart(dw,@dateCnt) WHEN 1 THEN '(日)' WHEN 2 THEN '(月)'  WHEN 3 THEN '(火)'  WHEN 4 THEN '(水)'  WHEN 5 THEN '(木)'  WHEN 6 THEN '(金)'  WHEN 7 THEN '(土)' END, 0, 0, 0, 0, @count, @week, 0)
			
			Set @count = @count + 1
			Set @group = @group + 1
			
			IF @group =8
				Begin
					--週間合計
					Insert into @tblMonthTyosa(tyosaDT, cntTyosaJishi, cntTyosain, cntTensaku1, cntTensaku2, cntCount, cntWeek, cntGokei)
					VALUES('週間合計', 0, 0, 0, 0, @count, @week, 1)
		
					Set @count = @count + 1
					Set @group = 1
					Set @week = @week + 1
				End

			
			Set @dateCnt = dateadd(d,1,@dateCnt)
		End
	
	WHILE @dateCnt > @datelast and @count<>0
		Begin     
			--月間合計
			Insert into @tblMonthTyosa(tyosaDT, cntTyosaJishi, cntTyosain, cntTensaku1, cntTensaku2, cntCount, cntWeek, cntGokei)
			VALUES('月間合計', 0, 0, 0, 0, @count, 0, 2)
			
			Set @count = 0
		End	
	
	SELECT  		
		*
	FROM @tblMonthTyosa 
 	ORDER BY cntCount

	return
GO

方法2：

drop proc xl_temp
go


CREATE Proc xl_Temp
	@tyosaDT varchar(7),
	@count	int output   ,
	@temp varchar output,
	@datetime datetime output
As
begin
	DECLARE @tblMonthTyosa TABLE(
		[tyosaDT][varchar](14),
		[cntTyosaJishi][int] ,
		[cntTyosain][int],
		[cntTensaku1][int],
		[cntTensaku2][int]
	) 

	DECLARE	@datefirst	datetime   
	DECLARE	@datelast	datetime
	DECLARE	@dateCnt	datetime
	/*DECLARE	@count	int   */
    	
	Set @count = 0

	/*開始時間*/
	Set @datefirst = cast(@tyosaDT + '/01' as datetime)
    

	while datepart(dw,@datefirst) <> 2
		Begin 
			Set @datefirst = cast(datediff(day,1,@datefirst) as datetime)	
		End

	/*結束時間*/
	Set @datelast = cast(datediff(d,1,dateadd(m,1,cast(@tyosaDT + '/01' as datetime))) as datetime)
    
	while datepart(dw,@datelast) <> 1
		Begin 
			Set @datelast = cast(dateadd(day,1,@datelast) as datetime)
		End
		
	/*時間*/
	Set @dateCnt = @datefirst

	Set @datetime = @datelast	

	WHILE @dateCnt <= @datelast
		Begin     
			Insert into @tblMonthTyosa(tyosaDT)
			VALUES(substring(CONVERT(char(10),@dateCnt,111),1,10) + CASE datepart(dw,@dateCnt) WHEN 1 THEN '(日)' WHEN 2 THEN '(月)'  WHEN 3 THEN '(火)'  WHEN 4 THEN '(水)'  WHEN 5 THEN '(木)'  WHEN 6 THEN '(金)'  WHEN 7 THEN '(土)' END)

			Set @count = @count +1
			Set @dateCnt = dateadd(d,1,@dateCnt)
		End

	SELECT  		
		*
	FROM @tblMonthTyosa 
 	ORDER BY tyosaDT
end
go

declare @cnttemp int
declare @temp varchar
declare @datetime datetime
exec xl_Temp '2008/09',@cnttemp output,@temp output,@datetime output
print @cnttemp
print @temp
print @datetime