Dynamic SQL Server stored procedure
- by Pinu
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]'.
