CODE WITH COMMON TABLE EXPRESSION CTE

Dear All.

DATABASE :: SQL SERVER 2008


I want to introduce CTE - Common Table Expression as part of the string.

How do I accomplish it.


An example will be welcomed,


DECLARE @sql nvarchar(4000)
SET @sql = 'SELECT A.BRN_NAME,
A.PROD_SERV,
A.COMP_TYPE,
P.PROD_SERV_TAT,
COUNT(*) AS OVER_CNT ,
LEFT JOIN BRANCH C ON A.BRN_NAME = C.BRN_NAME
LEFT JOIN PROD_SERV P ON A.PROD_SERV = P.PROD_SERV_NAME
LEFT JOIN PAYMAST B ON A.LNAME = B.LNAME
AND A.FNAME = B.FNAME
AND A.EMPNO = B.EMPNO
WHERE (A.COMP_DATE BETWEEN @CFROM AND @CTO)'


IF @CINCIDENT <>'ALL'
BEGIN
IF @CINCIDENT = 'OPENED'
SET @sql = @sql + ' AND A.COMP_DATE+E.ACTUAL_DAYS >= GETDATE()'
ELSE
SET @sql = @sql + ' AND A.COMP_DATE+E.ACTUAL_DAYS < GETDATE()'
END
------------------------------------
---------BRANCH---------------------
------------------------------------
IF @CDDL1 <>'ALL'
SET @sql = @sql + ' AND C.BRN_NAME=@CDDL1'



--------------------------
--------------------------
---GROPUPING & ORDERING---
--------------------------
--------------------------

SET @sql = @sql + ' GROUP BY A.BRN_NAME,
A.PROD_SERV,
A.COMP_TYPE,
A.DATE_SERV_COMP,
P.PROD_SERV_TAT'


EXECUTE sp_executesql @sql, N'@CINCIDENT VARCHAR(50), @CDDL1 VARCHAR(50),
@CFROM DATETIME , @CTO DATETIME'
, @CINCIDENT = @CINCIDENT
, @CDDL1 = @CDDL1
, @CFROM = @CFROM
, @CTO = @CTO



Thanks