Dynamic SQL Server stored procedure

Posted by Pinu on Stack Overflow See other posts from Stack Overflow or by Pinu
Published on 2010-04-07T14:25:16Z Indexed on 2010/04/07 14:33 UTC
Read the original article Hit count: 358

Filed under:
 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]'.

© Stack Overflow or respective owner

Related posts about sql-server