INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Dynamic SQL - Where clause help

Dynamic SQL - Where clause help

(OP)
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

RE: Dynamic SQL - Where clause help

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close