title: dynamic sql query with FOR XML Path
body: <p>I have following sql and when i run this its throw error </p>
  
  <p><strong><code>Incorrect syntax near '1'.</code></strong></p>
  
  <pre><code>declare @sql      nvarchar(max)
  
   set @sql =N'SELECT PT.P_Name as Prisoner_Type,      
      PE.PID_No,PE.Jail_Code,PE.Entry_Year,Convert(varchar(10),PE.Admission_Date,103) Admission_Date,PE.JID_No,Convert(varchar(10),PE.Release_Date,103) Release_Date ,  
      PM.Prisoner_Name, SUBSTRING(PM.Father_Name, 0, 17)as Father_Name, PM.Mother_Name,PM.Spouse_Name,PM.Caste_Code,
      PM.Religion_Code,PM.Occupation_Code,PM.Qualification,PM.Tendency_Type,PM.Risk_Type,PM.Annual_Income_Code,PM.Marital_Status_Code,PM.Id_Mark,
      PM.Sex_Code,PM.Nationality_Code,PM.Off_Code,PM.Present_Add, Age as age ,PM.Permt_Add, LS.Location  AS Location 
      ,NT.Nation_Name , JT.Jail_Address
      ,OT.Offence_Name,QT.Qualification as quali ,IT.Annual_Income_Range,CT.Caste_Name
      ,RT.Religion_Name,MT.Marital_Status_Name,OFT.Occ_Name AS Occupation
      , STUFF((    SELECT '', '' + cc.Act_Sec AS [text()]
  
      FROM CourtCases cc
      WHERE
      cc.PID_NO = PE.PID_NO and ('','' + Replace(pe.Case_No,''$'','','') +'','' LIKE ''%,'' + CONVERT(VARCHAR, cc.Case_No) + '',%'') group by cc.Act_Sec
      FOR XML PATH('''')     ),'+ cast(1 as nvarchar(10))+', '+cast(1 as nvarchar(10))+', '''' )
  
      AS [Act_Sec]
      , STUFF((    SELECT '', '' + Convert(varchar(10),CC.Fir_Date,103) AS [text()]
      FROM CourtCases cc
      WHERE
      cc.PID_NO = PE.PID_NO and ('','' + Replace(pe.Case_No,''$'','','') +'','' LIKE ''%,'' + CONVERT(VARCHAR, cc.Case_No) + '',%'') group by  Convert(varchar(10),CC.Fir_Date,103) 
      FOR XML PATH('''')     ),'+ cast(1 as nvarchar(10))+', '+cast(1 as nvarchar(10))+', '''' )
  
      AS [Fir_Date]
  
      ,STUFF((    SELECT '', '' + FIR_No AS [text()]
      FROM CourtCases cc
      WHERE
      cc.PID_NO = PE.PID_NO and ('','' + Replace(pe.Case_No,''$'','','') +'','' LIKE ''%,'' + CONVERT(VARCHAR, cc.Case_No) + '',%'') group by FIR_No
      FOR XML PATH('''')     ),'+ cast(1 as nvarchar(10))+', '+cast(1 as nvarchar(10))+', '''' )
  
      AS [FIR_No]
  
      ,  STUFF((    SELECT '', '' + ct.Court_Name AS [text()]
  
      FROM CourtCases cc inner join Court_Tab as ct 
      ON cc.Court_Code=convert(nvarchar(50),ct.Court_Code)
      WHERE
      cc.PID_NO = PE.PID_NO and ('','' + Replace(pe.Case_No,''$'','','') +'','' LIKE ''%,'' + CONVERT(VARCHAR, cc.Case_No) + '',%'') group by  ct.Court_Name
      FOR XML PATH('''')     ),'+ cast(1 as nvarchar(10))+', '+cast(1 as nvarchar(10))+', '''' )
      AS [Court_Name]
  
      , STUFF((    SELECT '', '' + pt.PS_Name AS [text()]                      
      FROM CourtCases cc inner join PS_Tab as pt 
      ON convert(nvarchar(50),cc.PS_Code)=convert(nvarchar(50),pt.PS_Code)
      WHERE
      cc.PID_NO = PE.PID_NO and ('','' + Replace(pe.Case_No,''$'','','') +'','' LIKE ''%,'' + CONVERT(VARCHAR, cc.Case_No) + '',%'') group by pt.PS_Name
      FOR XML PATH('''')     ),'+ cast(1 as nvarchar(10))+', '+cast(1 as nvarchar(10))+', '''' )
  
      AS [PS_Name]
  
      FROM PrisonMaster AS PM 
      INNER  JOIN   PrisonerEntry     AS PE        ON PE.PID_No = PM.PID_No  AND   PE.Jail_Code='+cast(@JailCode as nvarchar) +
  
      'INNER   JOIN  Nation_Tab       AS NT        ON PM.Nationality_Code = NT.Nation_Code
      INNER JOIN  Sex_Tab         AS S         ON PM.Sex_Code = S.Sex_Code
  
      INNER  JOIN  Jail_Tab       AS JT        ON PE.Jail_Code = JT.Jail_Code
      INNER   JOIN  Prisoner_Tab   AS PT        ON PE.Prisoner_Type=PT.P_Type
      INNER  JOIN  Loc_Status     AS LS        ON PE.Location=LS.Loc_Status
      left JOIN   Offence_Tab AS    OT        ON OT.Offence_Code=PM.Occupation_Code
      left JOIN Qualification_Tab AS QT       ON QT.Q_Code=PM.Qualification
      left JOIN Income_Tab AS IT               ON IT.Annual_Income_Code=PM.Annual_Income_Code
      left JOIN Caste_Tab AS CT                ON CT.Caste_Code=PM.Caste_Code
      LEFT JOIN Religion_Tab AS RT             ON RT.Religion_Code=PM.Religion_Code
      LEFT JOIN Marital_Tab AS MT              ON MT.Marital_Status_Code=PM.Marital_Status_Code
      LEFT JOIN Occupation_Tab AS OFT          ON     OFT.Occ_Code=PM.Occupation_Code
      WHERE   
  
      PE.Admission_Date BETWEEN '+cast(@DATE1 as nvarchar(50))+' and '+ cast(@DATE2 as nvarchar(50))
  
      exec (@sql)
  </code></pre>