Click here to Skip to main content
15,124,780 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more: , +
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

What I have tried:

    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
Posted
Updated 17-Nov-17 4:54am

1 solution

Changed this line from

SQL
EXEC sp_executesql @sSQL, @Depot, @Class


To:

SQL
EXEC sp_executesql @sSQL
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900