Dynamic SQL - Where clause help

[pndMaster]

Hello,

I have requirement to construct where clause based on parameters that was passed. If any parameter is null then do not include it in where clause if it is not null then include it in where clause.

For example
The Stored Procedure takes 4 input parameters

ClientId
ClientLName
ClientFName
ClientDOB

ClientId is required but others are not and could be null

so if ClientLName is null and ClientFName is null and ClientDOB is null then where clause would be

WHERE ClientId=@ClientId

otherwise if ClientLName is not null and others are null then
WHERE ClientId=@ClientId And ClientLName=@ClientLName

OR If client lname is not null and clientfname is not null then where clause would be
WHERE ClientId=@ClientId And ClientLName=@ClientLName and clientfname=@clientfname

last condition is where all have value
WHERE ClientId=@ClientId And ClientLName=@ClientLName and clientfname=@clientfname and clientdob=@clientdob

how can i do this in stored procedure.
the result is select * from dbo.client (dynamic where clause)

Regards

 

[Motlatjo]

Hi pndMaster

I was thinking about something along this lines
Tell me what you think


DECLARE @ClientId INT = 1
, @ClientLName VARCHAR(20) = 'Motlatjo'
, @ClientFName VARCHAR(20) =NULL
, @ClientDOB DATETIME
;WITH pndMaster
AS
(
SELECT 1 ClientId
, 'pnd' ClientLName
, 'Master' ClientFName
, GETDATE() ClientDOB
UNION
SELECT 2 ClientId
, 'Motlatjo' ClientLName
, 'Makgato' ClientFName
, GETDATE() ClientDOB
)
SELECT * FROM pndMaster
WHERE (ClientId = CASE WHEN ClientId IS NOT NULL THEN @ClientId END
OR ClientLName = CASE WHEN ClientLName IS NOT NULL THEN @ClientLName END
OR ClientFName = CASE WHEN ClientLName IS NOT NULL THEN @ClientFName END)

Regards