Thursday, 1 May 2014

Where Clause Dynamic

CREATE
PROCEDURE [dbo].[P_WhereClauseDynamic]
(

@Customer VARCHAR(50)= NULL,
@FirstName VARCHAR(50) = NULL,
@LastName VARCHAR(50) = NULL,
@ComplaintReference VARCHAR(50) = NULL,
@ZipCode VARCHAR(50) = NULL
)

AS


BEGIN

SET NOCOUNT ON;
DECLARE @SQL varchar(MAX)
DECLARE @listStr VARCHAR(MAX)
DECLARE @Table TABLE(SQLValue varchar(50))

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


IF (@Customer IS NULL AND @FirstName IS NULL AND @LastName IS NULL AND @ComplaintReference IS NULL AND @ComplaintReference IS NULL AND @ZipCode IS NULL)

BEGIN

SELECT DISTINCT TOP 0
c.UniqueID_Customer_Idnt
,c.Customer
,c.Title
,c.FirstName
,c.LastName
,c.Add_Line_1
,c.ZipCode
FROM dbo.Customer c
INNER JOIN dbo.Account l
ON (c.UniqueID_Customer_Idnt = l.UniqueID_Customer_Idnt)
LEFT JOIN Complaint cp
ON (c.UniqueID_Customer_Idnt = cp.UniqueID_Customer_Idnt)
END

ELSE
BEGIN

SET @SQL = 'SELECT DISTINCT
c.UniqueID_Customer_Idnt
,c.Customer
,c.Title
,c.FirstName
,c.LastName
,c.Add_Line_1
,c.ZipCode
FROM dbo.Customer c
INNER JOIN dbo.Account l
ON c.UniqueID_Customer_Idnt = l.UniqueID_Customer_Idnt
LEFT JOIN Complaint cp
ON c.UniqueID_Customer_Idnt = cp.UniqueID_Customer_Idnt WHERE '

 
IF @FirstName IS NOT NULL
BEGIN
INSERT INTO @Table
SELECT 'c.FirstName = ' +''''+ @FirstName + ''''
END
IF @LastName IS NOT NULL
BEGIN
INSERT INTO @Table
SELECT 'c.LastName = ' +''''+ @LastName + ''''
END
IF @Customer IS NOT NULL
BEGIN
INSERT INTO @Table
SELECT 'c.Customer = ' +''''+ @Customer + ''''
END
IF @ComplaintReference IS NOT NULL
BEGIN
INSERT INTO @Table
SELECT 'cp.ComplaintReferenceerence = ' +''''+ @ComplaintReference + ''''
END
IF @ZipCode IS NOT NULL
BEGIN
INSERT INTO @Table
SELECT 'c.ZipCode = ' +''''+ @ZipCode + ''''
END
SELECT @listStr = COALESCE(@listStr+' AND ' , '') + ISNULL(SQLValue,'') FROM @Table
--SELECT @listStr

SET @SQL = @SQL + @listStr
PRINT @SQL
EXEC(@SQL)

END

SET NOCOUNT OFF;
END