Generate calendar using TSQL

DECLARE @Month AS INT = 4 --Set the MONTH for which you want to generate the Calendar.
DECLARE @Year AS INT = 2013 --Set the YEAR for which you want to generate the Calendar.
--Find and set the Start & End Date of the said Month-Year
DECLARE @StartDate AS DATETIME = CONVERT(VARCHAR, @Year) + RIGHT('0' + CONVERT(VARCHAR, @Month), 2) + '01'
DECLARE @EndDate AS DATETIME = DATEADD(DAY, - 1, DATEADD(MONTH, 1, @StartDate));

WITH Dates
AS (
 SELECT @StartDate Dt

 UNION ALL

 SELECT DATEADD(DAY, 1, Dt)
 FROM Dates
 WHERE DATEADD(DAY, 1, Dt) <= @EndDate
 ),
Details
AS (
 SELECT DAY(Dt) CDay,
  DATEPART(WK, Dt) CWeek,
  MONTH(Dt) CMonth,
  YEAR(Dt) CYear,
  DATENAME(WEEKDAY, Dt) DOW,
  Dt
 FROM Dates
 )
--Selecting the Final Calendar
SELECT Sunday,
 Monday,
 Tuesday,
 Wednesday,
 Thursday,
 Friday,
 Saturday
FROM (
 SELECT CWeek,
  DOW,
  CDay
 FROM Details
 ) D
PIVOT(MIN(CDay) FOR DOW IN (
   Sunday,
   Monday,
   Tuesday,
   Wednesday,
   Thursday,
   Friday,
   Saturday
   )) AS PVT
ORDER BY CWeek


output:

Difference between stored procedure and functions (UDF)

Functions

•     can be used with Select statement
•     Not returning output parameter but returns Table variables
•     You can join UDF
•     Can not be used to change server configuration
•     Can not be used with XML FOR clause
•     Can not have transaction within function

Stored Procedure

•     have to use EXEC or EXECUTE
•     return output parameter
•     can create table but won’t return Table Variables
•     you can not join SP
•     can be used to change server configuration
•     can be used with XML FOR Clause
•     can have transaction within SP

Functions

• can be used with Select statement


• Not returning output parameter but returns Table variables


• You can join UDF


• Can not be used to change server configuration


• Can not be used with XML FOR clause


• Can not have transaction within function

Stored Procedure

• have to use EXEC or EXECUTE


• return output parameter


• can create table but won’t return Table Variables


• you can not join SP


• can be used to change server configuration


• can be used with XML FOR Clause


• can have transaction within SP

- See more at: http://www.sql-server-citation.com/2007/02/difference-between-stored-procedure-and.html#sthash.kf7AqivX.dpuf

Functions

• can be used with Select statement


• Not returning output parameter but returns Table variables


• You can join UDF


• Can not be used to change server configuration


• Can not be used with XML FOR clause


• Can not have transaction within function

Stored Procedure

• have to use EXEC or EXECUTE


• return output parameter


• can create table but won’t return Table Variables


• you can not join SP


• can be used to change server configuration


• can be used with XML FOR Clause


• can have transaction within SP

- See more at: http://www.sql-server-citation.com/2007/02/difference-between-stored-procedure-and.html#sthash.kf7AqivX.dpuf

Functions

• can be used with Select statement


• Not returning output parameter but returns Table variables


• You can join UDF


• Can not be used to change server configuration


• Can not be used with XML FOR clause


• Can not have transaction within function

Stored Procedure

• have to use EXEC or EXECUTE


• return output parameter


• can create table but won’t return Table Variables


• you can not join SP


• can be used to change server configuration


• can be used with XML FOR Clause


• can have transaction within SP

- See more at: http://www.sql-server-citation.com/2007/02/difference-between-stored-procedure-and.html#sthash.kf7AqivX.dpuf