Incorrect syntax found during sp_executesql

The following script I have tried to run in MS server management studio and this is work perfectly fine.

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 <> 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)>0 
    AND c.DateConfirmed >= '2010-08-06 00:00:00' 
    AND c.DateConfirmed <= '2010-08-06 23:59:59' 
ORDER BY ucb.UserFirstName, ucb.UserLastName, c.caseid

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').

--  /* 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 <> 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) > 0'


    SET @SQL = @SQL+' AND c.DateConfirmed >= '''+@actualstartdate+''' 
                AND c.DateConfirmed <= '''+@actualenddate+'''
                ORDER BY ucb.UserFirstName, ucb.UserLastName, c.caseid'

    EXECUTE sp_executesql @SQL

Does anyone know what is going on here? and how can I fix this issue??

do a print @sql AKA the poor man's debugger and the problem will be revealed, my guess is that nvarchar(500) is not enough..make it 2000

Also take a look at Changing exec to sp_executesql doesn't provide any benefit if you are not using parameters correctly to see how to use parameter so that execution plans can be reused

You have more than 500 chars, so @SQL got truncated.
change to:

  DECLARE @SQL nvarchar(4000)

Have you tried printing @SQL to see what is inside the variable?

Taking what you have and printing it, I get this:

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  AND c.CaseStatusID <> 7 
                AND (SELECT COUNT(ServiceID) 
                FROM Cases ca 
                    JOIN Services s ON ca.CaseI

It looks like your @SQL variable isn't big enough -- try nvarchar(max).