CTE is working as expected but why dynamic CTE is not working correct ?

Question

Hi,
When I try to call direct CTE then it works but when I am calling it dynamically then it is throwing errors.

DECLARE  
    @s nvarchar(MAX) = N'',  
    @firstName nvarchar(40) = N'omer';  
	SET @s = N' ;WITH CTECount AS (  
Select ba.Id, ar.RenterUserId,ad.MainDriverUserId,ba.RentalAgreementId, ba.AgreementStatus, ba.FranchiseId,  
ROW_NUMBER() over (partition by ar.RenterUserId,ad.MainDriverUserId order by ar.RenterUserId desc) RowNo ,  
(case when ba.RentalAgreementId <> '' then Count(ba.Id)  
OVER (Partition by (case when ba.RentalAgreementId <> '' then ar.RenterUserId else 0 end)) else 0 end)  RACount,  
(case when ba.RentalAgreementId = '' then Count(ba.Id)  
OVER (Partition by (case when ba.RentalAgreementId = '' then ad.MainDriverUserId else 0 end)) else 0 end) ResCount  
from BookingAgreements ba with (nolock)	  
left join AgreementRenters ar with (nolock) on ar.AgreementId = ba.Id and ar.FranchiseId = ba.FranchiseId  
left join AgreementDrivers ad with (nolock) on ad.AgreementId = ba.Id and ad.FranchiseId = ba.FranchiseId  
) Select * FROM CTECount;';  
  
EXEC sys.sp_executesql  
    @s;  

Please see the errors below :

Msg 156, Level 15, State 1, Line 5  
Incorrect syntax near the keyword 'else'.  
Msg 319, Level 15, State 1, Line 8  
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.  
Msg 319, Level 15, State 1, Line 9  
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.  
Msg 319, Level 15, State 1, Line 10  
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.  

Answer 1

Hi @mehmood tekfirst ,

As JingyangLi said, you can try the following code:

  DECLARE  
     @s nvarchar(MAX) = N'',  
     @firstName nvarchar(40) = N'omer';  
     SET @s = N' ;WITH CTECount AS (  
 Select ba.Id, ar.RenterUserId,ad.MainDriverUserId,ba.RentalAgreementId, ba.AgreementStatus, ba.FranchiseId,  
 ROW_NUMBER() over (partition by ar.RenterUserId,ad.MainDriverUserId order by ar.RenterUserId desc) RowNo ,  
 (case when ba.RentalAgreementId <> " " then Count(ba.Id)  
 OVER (Partition by (case when ba.RentalAgreementId <>  " " then ar.RenterUserId else 0 end)) else 0 end)  RACount,  
 (case when ba.RentalAgreementId = " " then Count(ba.Id)  
 OVER (Partition by (case when ba.RentalAgreementId = " " then ad.MainDriverUserId else 0 end)) else 0 end) ResCount  
 from BookingAgreements ba with (nolock)      
 left join AgreementRenters ar with (nolock) on ar.AgreementId = ba.Id and ar.FranchiseId = ba.FranchiseId  
 left join AgreementDrivers ad with (nolock) on ad.AgreementId = ba.Id and ad.FranchiseId = ba.FranchiseId  
 ) Select * FROM CTECount;'  
      
 EXEC sys.sp_executesql  
 @s;  

Best regards
Niko

Answer 2

You need to escape the single quote in dynamic sql. You need to double up single quotes. two '' will become four inside the dyname sql string ''''.

Answer 3

Thank you. this issue has been fixed.