title: Incorrect syntax found during sp_executesql
body: <p>The following script I have tried to run in MS server management studio and this is work perfectly fine.</p>
  
  <pre><code>SELECT ucb.UserFirstName, ucb.UserLastName, ucb.userid, c.caseid
  FROM Cases c 
      LEFT JOIN Users ucb ON (c.ConfirmedBy=ucb.UserID) 
      JOIN RealtorStaff rs ON c.realtorstaffid=rs.realtorstaffid 
  WHERE c.ConfirmedBy is not null AND c.CaseStatusID &lt;&gt; 7 
      AND (
              SELECT COUNT(ServiceID) 
              FROM Cases ca JOIN Services s ON ca.CaseID=s.CaseID 
              WHERE ca.CaseID=c.CaseID 
                  AND s.ServiceTypeID != 8 
                  AND s.ServiceTypeID != 9 
                  AND s.ServiceTypeID != 10 
                  AND s.ServiceTypeID != 14 
                  AND s.ServiceTypeID != 15 
                  AND s.ServiceStatusID != 7)&gt;0 
      AND c.DateConfirmed &gt;= '2010-08-06 00:00:00' 
      AND c.DateConfirmed &lt;= '2010-08-06 23:59:59' 
  ORDER BY ucb.UserFirstName, ucb.UserLastName, c.caseid
  </code></pre>
  
  <p>Somehow after I tried to convert it into the following scrip so that I can run it under sp_executesql and error message display (Msg 102, Level 15, State 1, Line 11
  Incorrect syntax near '8'). </p>
  
  <pre><code>--  /* for testing purpose
      Declare @startdate DATETIME; set @startdate = '2010-08-06'
      Declare @enddate DATETIME; set @enddate = '2010-08-06'
  --  */
  
      Declare @actualstartdate varchar(20); set @actualstartdate = replace(convert(char(26),@startdate,102),'.','-')+' 00:00:00'
      Declare @actualenddate varchar(20); set @actualenddate = replace(convert(char(26),@enddate,102),'.','-')+' 23:59:58'
  
      DECLARE @SQL nvarchar(500)
  
      SET @SQL = 'SELECT ucb.UserFirstName, ucb.UserLastName, ucb.userid, c.caseid
                   INTO ##actual_assigned_cases
                   FROM Cases c 
                      LEFT JOIN Users ucb ON (c.ConfirmedBy=ucb.UserID) 
                      JOIN RealtorStaff rs ON c.realtorstaffid=rs.realtorstaffid 
                   WHERE c.ConfirmedBy is not null '
  
      SET @SQL = @SQL+' AND c.CaseStatusID &lt;&gt; 7 
                  AND (SELECT COUNT(ServiceID) 
                  FROM Cases ca 
                      JOIN Services s ON ca.CaseID=s.CaseID 
                  WHERE ca.CaseID=c.CaseID 
                  AND s.ServiceTypeID != 8
                  AND s.ServiceTypeID != 9
                  AND s.ServiceTypeID != 10 
                  AND s.ServiceTypeID != 14
                  AND s.ServiceTypeID != 15 
                  AND s.ServiceStatusID != 7) &gt; 0'
  
  
      SET @SQL = @SQL+' AND c.DateConfirmed &gt;= '''+@actualstartdate+''' 
                  AND c.DateConfirmed &lt;= '''+@actualenddate+'''
                  ORDER BY ucb.UserFirstName, ucb.UserLastName, c.caseid'
  
      EXECUTE sp_executesql @SQL
  </code></pre>
  
  <p>Does anyone know what is going on here? and how can I fix this issue??</p>