Dynamic SQL Server stored procedure

Question

 ALTER PROCEDURE [dbo].[GetDocumentsAdvancedSearch]
          @SDI  CHAR(10) = NULL
             ,@Client CHAR(4) = NULL
             ,@AccountNumber VARCHAR(20) = NULL
         ,@Address VARCHAR(300)  = NULL
         ,@StartDate DATETIME = NULL
         ,@EndDate DATETIME = NULL
         ,@ReferenceID CHAR(14) = NULL
    AS
    BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

      -- DECLARE
      DECLARE @Sql        NVARCHAR(4000)
      DECLARE @ParamList  NVARCHAR(4000)

      SELECT  @Sql = 'SELECT 
                        DISTINCT ISNULL(Documents.DocumentID, '') 
                       ,Person.Name1
                       ,Person.Name2
                       ,Person.Street1
                       ,Person.Street2
                       ,Person.CityStateZip
                       ,ISNULL(Person.ReferenceID,'')
                       ,ISNULL(Person.AccountNumber,'')
                       ,ISNULL(Person.HasSetPreferences,0)
                       ,Documents.Job
               ,Documents.SDI
                       ,Documents.Invoice
                       ,ISNULL(Documents.ShippedDate,'')
                       ,ISNULL(Documents.DocumentPages,'')
                       ,Documents.DocumentType
                       ,Documents.Description
                       FROM
                       Person
                       LEFT OUTER JOIN Documents ON Person.PersonID = Documents.PersonID
                       LEFT OUTER JOIN DocumentType ON Documents.DocumentType = DocumentType.DocumentType
                       LEFT OUTER JOIN Addressess   ON Person.PersonID = Addressess.PersonID'

       SELECT @Sql = @Sql + ' WHERE 
                       Documents.SDI IN ( '+ QUOTENAME(@sdi) + ') OR (Person.AssociationID = ' + ''' 000000 + ''' + 'AND Person.Client = ' + QUOTENAME(@Client)

        IF NOT (@AccountNumber IS  NULL)
        SELECT @Sql = @Sql + 'AND Person.AccountNumber LIKE' + QUOTENAME(@AccountNumber)
        IF NOT (@Address IS  NULL)
        SELECT @Sql = @Sql + 'AND Person.Name1 LIKE' +QUOTENAME(@Address)+ 'AND Person.Name2 LIKE' +QUOTENAME(@Address)+ 'AND Person.Street1 LIKE' +QUOTENAME(@Address)+ 'AND Person.Street2 LIKE' +QUOTENAME(@Address)+ 'AND Person.CityStateZip LIKE' +QUOTENAME(@Address)
        IF NOT (@StartDate IS  NULL)
        SELECT @Sql = @Sql + 'AND Documents.ShippedDate >=' +@StartDate
        IF NOT (@EndDate IS NULL)
        SELECT @Sql = @Sql + 'AND Documents.ShippedDate <=' +@EndDate
        IF NOT (@ReferenceID IS  NULL)
        SELECT @Sql = @Sql + 'AND Documents.ReferenceID =' +QUOTENAME(@ReferenceID)

        -- Insert statements for procedure here
       -- PRINT @Sql
        SELECT @ParamList = '@Psdi CHAR(10),@PClient CHAR(4),@PAccountNumber VARCHAR(20),@PAddress VARCHAR(300),@PStartDate DATETIME ,@PEndDate DATETIME,@PReferenceID CHAR(14)'  
        EXEC SP_EXECUTESQL @Sql,@ParamList,@Sdi,@Client,@AccountNumber,@Address,@StartDate,@EndDate,@ReferenceID
        --PRINT @Sql


    END

ERROR

Msg 102, Level 15, State 1, Line 23 Incorrect syntax near '000000'. Msg 105, Level 15, State 1, Line 23 Unclosed quotation mark after the character string 'AND Person.Client = [1 ]AND Person.AccountNumber LIKE[1]'.

Answer 1

Check out these lines of your statement:

'AND Person.AccountNumber LIKE' + QUOTENAME(@AccountNumber)
'AND Person.Name1 LIKE' +QUOTENAME(@Address)+ 'AND Person.Name2 LIKE' +QUOTENAME(@Address)+ 'AND Person.Street1 LIKE' +QUOTENAME(@Address)+ 'AND Person.Street2 LIKE' +QUOTENAME(@Address)+ 'AND Person.CityStateZip LIKE' +QUOTENAME(@Address)

This will result in something like

AND Person.AccountNumber LIKE[13123]AND Person.Name1 LIKE[bla]AND Person.Name2 LIKE[alskda] 

and so forth - you need to start adding some spacing!

• put a space between your LIKE keyword and the value it's referring to:

   AND Person.AccountNumber LIKE ''' + @AccountNumber + '''' +
  

• put a space before every AND in your statements

   SELECT @Sql = @Sql + ' AND Person.Name1 LIKE ''' + @Address + 
                 ''' AND Person.Name2 LIKE ''' + @Address + 
                 ''' AND Person.Street1 LIKE ''' + @Address + 
                 ''' AND Person.Street2 LIKE '  ........
  

Answer 2

Just stop using QUOTENAME for your values. It's not supposed to be used for that.

EDIT You also have several ANDs where there is not space in front. Make sure to add spaces so that you don't get something like bla=0AND ...