Date & Day Name of Current Month in SQL Server Stored Procedure

This is a SQL Stored Procedure which will return a table having dates and day names of the current month. This will be helpful for any calender control related coding.

Create Procedure [dbo].[Current_Month_Days]

As
Begin

Declare @myDate Datetime
Set @myDate = getdate()

Declare @LastDay int

Set @LastDay = (SELECT DATENAME(d,(DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)))))

-- Select @LastDay

Create Table #temptable( DateField Datetime, DayField Varchar(10))

DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <= @LastDay)
BEGIN

insert into #temptable(DateField,DayField) values((DATEADD(dd,-(DAY(@mydate)-@intFlag),@mydate)),(DATENAME(dw , (DATEADD(dd,-(DAY(@mydate)-@intFlag),@mydate))) ))

SET @intFlag = @intFlag + 1

END

Select * From #temptable

End