CREATE PROCEDURE dbo.GetdaysInMonth

(

-- Add the parameters for the function here

@year varchar(20),            ' Any year      

@month varchar(20),           ' Between 1 to 12

@DayNo as int       ' 1 (Sun), 2 (Mon), 3 (Tue),4 (Wed), 5 (Thu), 6(Fri), 7(Sat)

)AS

declare @day1 varchar(20)

set @day1=1

 declare @DtFrom datetime

declare @DtTo datetime

declare @DayName VARCHAR(12)

select @DtFrom=convert(datetime,@month +'/'+@day1+'/'+@year,101)

SELECT @DtTo=convert(datetime,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DtFrom)+1,0)),101) 

declare @DateList TABLE ([Day] varchar(20),Dt datetime)

BEGIN

Select @DayName=

case 

when @DayNo=1 then 'Sunday' 

when @DayNo=2 then 'Monday'

when @DayNo=3 then 'Tuesday'

when @DayNo=4 then 'Wednesday'

when @DayNo=5 then 'Thursday'

when @DayNo=6 then 'Friday'

when @DayNo=7 then 'Saturday'

end 

IF NOT (@DayName = 'Monday' OR @DayName = 'Sunday' OR @DayName = 'Tuesday' OR @DayName = 'Wednesday' OR @DayName = 'Thursday' OR @DayName = 'Friday' OR @DayName = 'Saturday')

BEGIN

--Error Insert the error message and return

INSERT INTO @DateList

SELECT 'Invalid Day',NULL AS DAT

RETURN

END 

DECLARE @TotDays INT

DECLARE @CNT INT

SET @TotDays = DATEDIFF(DD,@DTFROM,@DTTO) -- [NO OF DAYS between two dates]

SET @CNT = 0

WHILE @TotDays >= @CNT -- repeat for all days 

BEGIN

-- Pick each single day and check for the day needed

IF Datename(DW,(@DTTO - @CNT)) = @DAYNAME

BEGIN

INSERT INTO @DateList

SELECT @DAYNAME,(@DTTO - @CNT) AS DAT

END

SET @CNT = @CNT + 1

END

select * FROM @DateList

RETURN



END