How to write recursive query in SQL Server?

Recursive query can be create in sql using stored procedure but you can also use CTE (Common table expression).
Syntax of CTE is as follows:

WITH cte_name ( column_name [,...n] )
AS
(
CTE_query_definition –- Anchor member is defined.
UNION ALL
CTE_query_definition –- Recursive member is defined referencing cte_name.
)
-- Statement using the CTE
SELECT *
FROM cte_name

--Example: Generate Week Numbers using CTE
DECLARE @Year int,@Month int
SELECT @Year=2013,@Month=3

;With CTE
AS
(
SELECT DATEADD(mm,@Month-1,DATEADD(yy,@Year-1900,0)) AS DateVal,1 AS WeekNo
UNION ALL
SELECT DATEADD(wk,1,DateVal),WeekNo + 1
FROM CTE
WHERE DATEADD(wk,1,DateVal) < DATEADD(mm,@Month,DATEADD(yy,@Year-1900,0))
)

SELECT DateVal,WeekNo
FROM CTE

OPTION (MAXRECURSION 0)

NOTE: we can make use of WHILE inside stored procedure to get the same result as above