Select from following answers:- CTE
- Stored Procedure
- Both 1 and 2

- None
- All Above
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
Show Correct Answer
Asked In: Many Interviews |
Alert Moderator