위의 쿼리에선 null 에 대한 비교가 안되고, insert 시 한행씩 추가되지 한 column 씩 추가되지 않아 원하는 값이 나오지 않읍니다.

create FUNCTION fn_prd_calendar
(
       @AS_FRDT CHAR(8),
       @AS_TODT CHAR(8)
)
RETURNS @prd_calendar TABLE
(
        DAY1    CHAR(8) ,
        DAY2    CHAR(8) ,
        DAY3    CHAR(8) ,
        DAY4    CHAR(8) ,
        DAY5    CHAR(8) ,
        DAY6    CHAR(8) ,
        DAY7    CHAR(8)
)
AS
BEGIN

DECLARE @DAY      CHAR(8) ,
               @day1     char(8) ,
               @day2     char(8) ,
               @day3     char(8) ,
               @day4     char(8) ,
               @day5     char(8) ,
               @day6     char(8) ,
               @day7     char(8) ,
               @AN_COUNT int

SET         @AN_COUNT = 1
set          @day      = ''

WHILE  (@DAY < @AS_TODT)
begin
       if (@an_count % 7 = 1)
          select @day1 = null, @day2 = null, @day3 = null, @day4 = null, @day5 = null, @day6 = null, @day7 = null

       SELECT @DAY = CONVERT(CHAR(8) , DATEADD(DAY , @AN_COUNT -1 , @AS_FRDT) , 112)

       select @day1 = (case when @an_count % 7 = 1 then @day else @day1 end),
              @day2 = (case when @an_count % 7 = 2 then @day else @day2 end),
              @day3 = (case when @an_count % 7 = 3 then @day else @day3 end),
              @day4 = (case when @an_count % 7 = 4 then @day else @day4 end),
              @day5 = (case when @an_count % 7 = 5 then @day else @day5 end),
              @day6 = (case when @an_count % 7 = 6 then @day else @day6 end),
              @day7 = (case when @an_count % 7 = 0 then @day else @day7 end)

       if (@an_count % 7 = 0)
           insert @prd_calendar values (@day1, @day2, @day3, @day4, @day5, @day6, @day7)          
       else
           begin
             if (@DAY = @AS_TODT)
                 insert @prd_calendar values (@day1, @day2, @day3, @day4, @day5, @day6, @day7)               
           end

       SET @AN_COUNT = @AN_COUNT + 1
end

RETURN
END

GO

select * from fn_prd_calendar('20030311','20030912')  --검색문
go

drop function fn_prd_calendar  --함수 삭제
go