Friday, September 18, 2009

Common Table Expressions in SQL Server 2005

a simple example for CTE's
Syntax of CTE's is

WITH CTEName (parameter1,parameter2,..)
AS (Simple Subquery)
SELECT...
FROM CTEName

example for genrate all date for a month from a single date

DECLARE @Dates datetime
set @dates='02/01/2009'
;WITH DayLists (DayDate) AS
(
SELECT @Dates
UNION ALL
SELECT DATEADD(d, 1, DayDate) FROM DayLists
WHERE (DayDate < DATEADD(d, -1, DATEADD(m, 1, @Dates))))
SELECT DayDate FROM DayLists






Tamilish






No comments:

Post a Comment