In Sql Server suppose you want to write a query to display numbers from 1 to 10 in sequence , then we either use while loop or cursor. If our requirement is for a simple task , then we can make use of cursors and loop. If it is little complex, then we have to write lot of code using a cursor . But by using CTE we can reduce number of lines of our code and make it simpler. To retrieve hierarchical data efficiently CTE can be used.
Sample Example – Common Table Expression
Example : Write a query to Output numbers from 1 to 10 in sequence.
| /*DISPLAY LIST OF NUMBERS FROM 1 to 10*/ BEGIN DECLARE @Start INT , @End INT SET @Start = 1 SET @End = 10 ;WITH Series AS ( SELECT @Start AS List --INITIALISATION PART UNION ALL SELECT List + 1 FROM Series WHERE List < @End --RECURSION PART ) SELECT * FROM Series /*changing recursion limit SELECT * FROM Series OPTION (MAXRECURSION 150) */ END GO |
Points to be remembered:
1) The previous statement must be terminated with a semicolon if we are using CTE , so place a semicolon before Common Table Expression.
2) By default Maximum No of recursions is limited to 100 . If the query undergoes more than 100 recursions, it throws an error.
3) If one want to change the maximum limit of transactions , then use OPTION (MAXRECURSION 200) in Select statement . Maximum recursion limit one can specify for integers is 32767.
4) To see intermediate output , then specify OPTION (MAXRECURSION @RecursionNo) . Suppose the query takes 10 recursions and we wish to see output after 2nd recursion , then include option(MAXRECURSION 2) in select statement.
Retrieve hierarchical data using CTE :
Example :We have employee table with 3 columns ( empid , empname , reportsto) employee id , name and his manager information. Now to generate report of an employee in hierarchy -- “to whom he reports and employees who report to him ( subordinates) and their subordinates also” .
| BEGIN DECLARE @TblEmployees AS TABLE ( EmployeeID INT PRIMARY KEY , EmpName VARCHAR(100), ReportsTo INT NULL ) INSERT INTO @TblEmployees VALUES( 1 , 'Ram' , NULL) INSERT INTO @TblEmployees VALUES( 2 , 'Raj' , NULL) INSERT INTO @TblEmployees VALUES( 3 , 'Ravi' , NULL) INSERT INTO @TblEmployees VALUES( 4 , 'Rakhi' , 1) INSERT INTO @TblEmployees VALUES( 5 , 'Rajesh' , 2) INSERT INTO @TblEmployees VALUES( 6 , 'Ravinder' , 3) INSERT INTO @TblEmployees VALUES( 7 , 'Sailender' , 3) INSERT INTO @TblEmployees VALUES( 8 , 'Vikram', 6) INSERT INTO @TblEmployees VALUES( 9 , 'Tarun' , 7) ;WITH ReportingOfficers AS ( SELECT EmployeeID,EmpName , ReportsTo FROM @TblEmployees WHERE EmployeeID = 3 --ReportsTo IS NULL UNION ALL SELECT E.EmployeeID , E.EmpName,E.ReportsTo FROM @TblEmployees E , ReportingOfficers M WHERE M.EmployeeID = E.ReportsTo ) SELECT * FROM ReportingOfficers END GO |
Here i queried for employee id 3.so the result contains details of employee id 3 , his manager details and his subordinates , and sub- subordinates data.
Output :
