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: 394
sql-server
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