sql stored procedure conditional where clause returning syntax error

Question

I have the following stored procedure which takes 2 parameters. The @Class parameter can have any one of 11 values. Depending on the value the where clause looks at a different column.

The procedure compiles ok and if I copy the print and paste it into a new query window it runs fine, but if I execute the procedure I am getting an Incorrect syntax error message near 'Bristol' but cannot figure out what I need to do to fix despite looking on here and on other web pages. Any help would be much appreciated

ALTER PROCEDURE [TTR_HazDriver]
@Depot nvarchar(50),
@Class nvarchar(1)

AS
BEGIN
DECLARE @Where nvarchar(1000)
DECLARE @sSql nvarchar(MAX)
DECLARE @Order nvarchar(1000)

SET @sSql = '
SELECT EM.EmployeeNumber
, EM.EmployeeSurname
, EM.EmployeeInitials
, D.Depot
, EDL.Class1
, EDL.Class2
, EDL.Class3
, EDL.Class4
, EDL.Class5
, EDL.Class6
, EDL.Class7
, EDL.Class8
, EDL.Class9
, CONVERT(VARCHAR(10),EDL.ExpiryDate, 103) ExpiryDate
, EDL.Tanks
, EDL.Package
FROM   EmployeeMaster EM 
LEFT OUTER JOIN PayrollFrequency PF ON EM.FrequencyDesc = PF.DescCode 
INNER JOIN EmployeeDrivingLicence EDL ON EM.EmpCode = EDL.EmpCode 
LEFT OUTER JOIN Depot D ON EM.Depot = D.DescCode'

SET @Where = '
WHERE (D.Depot = ''' + @Depot + ''' OR ''' + @Depot + ''' IS NULL)
AND EM.EmployeeLeft = ''N''
AND PF.FrequencyDesc = ''Weekly'''



SET @Order = '
    ORDER BY D.DepotDepotDescription
    , EDL.ExpiryDate'

IF @Class = '1' 
SET @Where = @Where + ' AND EDL.Class1 = ''Y'''
IF @Class = '2' 
SET @Where = @Where + ' AND EDL.Class2 = ''Y'''
IF @Class = '3' 
SET @Where = @Where + ' AND EDL.Class3 = ''Y'''
IF @Class = '4' 
SET @Where = @Where + ' AND EDL.Class4 = ''Y'''
IF @Class = '5' 
SET @Where = @Where + ' AND EDL.Class5 = ''Y'''
IF @Class = '6' 
SET @Where = @Where + ' AND EDL.Class6 = ''Y'''
IF @Class = '7' 
SET @Where = @Where + ' AND EDL.Class7 = ''Y'''
IF @Class = '8' 
SET @Where = @Where + ' AND EDL.Class8 = ''Y'''
IF @Class = '9' 
SET @Where = @Where + ' AND EDL.Class9 = ''Y'''
IF @Class = 'T' 
SET @Where = @Where + ' AND EDL.Tanks = ''Y'''
IF @Class = 'P' 
SET @Where = @Where + ' AND EDL.Package = ''Y'''


SET @sSql = @sSql + @Where + @Order
PRINT @sSql

EXEC sp_executesql @sSQL, @Depot, @Class
END

The print statement produces the following query:

SELECT EM.EmployeeNumber
 , EM.EmployeeSurname
 , EM.EmployeeInitials
 , D.Depot
 , EDL.Class1
 , EDL.Class2
 , EDL.Class3
 , EDL.Class4
 , EDL.Class5
 , EDL.Class6
 , EDL.Class7
 , EDL.Class8
 , EDL.Class9
 , CONVERT(VARCHAR(10),EDL.ExpiryDate, 103) ExpiryDate
 , EDL.Tanks
 , EDL.Package
 FROM   EmployeeMaster EM 
 LEFT OUTER JOIN PayrollFrequency PF ON EM.FrequencyDesc = PF.DescCode 
 INNER JOIN EmployeeDrivingLicence EDL ON EM.EmpCode = EDL.EmpCode 
 LEFT OUTER JOIN Depot D ON EM.DepotDepotDescription = D.DescCode
 WHERE (D.DepotDepotDescription = 'Bristol' OR 'Bristol' IS NULL)
 AND PF.FrequencyDesc = 'Weekly'
 AND EDL.Class3 = 'Y'
    ORDER BY D.Depot
    , EDL.ExpiryDate

Answer 1

Since you're embedding the parameter values into the query variable, you don't need to add the params to the sp_executesql call, so this should work:

EXEC sp_executesql @sSQL

Instead of:

EXEC sp_executesql @sSQL, @Depot, @Class

Here's a full working example with DDL statements - swap out the last line with the original version, and you'll get the syntax error.

CREATE TABLE EmployeeMaster (EmployeeNumber INT, EmployeeSurname VARCHAR(25), EmployeeInitials VARCHAR(10), FrequencyDesc VARCHAR(25), EmpCode VARCHAR(25), Depot VARCHAR(25), EmployeeLeft VARCHAR(1))
CREATE TABLE PayrollFrequency (DescCode VARCHAR(25), FrequencyDesc VARCHAR(25))
CREATE TABLE EmployeeDrivingLicence (EmpCode VARCHAR(25), Class1 VARCHAR(1), Package VARCHAR(1), Tanks VARCHAR(1))
ALTER TABLE EmployeeDrivingLicence ADD ExpiryDate DATETIME
CREATE TABLE Depot (Depot VARCHAR(25), DescCode VARCHAR(25), DepotDepotDescription VARCHAR(25))

CREATE PROCEDURE [TTR_HazDriver]
@Depot nvarchar(50),
@Class nvarchar(1)

AS
BEGIN

...

EXEC sp_executesql @sSQL
END
GO

EXEC [TTR_HazDriver] 'test', 'P'