Incorrect syntax found during sp_executesql

Question

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??

Answer 1

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

Answer 2

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

Answer 3

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

  DECLARE @SQL nvarchar(4000)