Conditional where clause in SQL server

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Hi Guys,

I want to write a stored procedure in sql server 2012.

in where clause, if one variable is null then search by another variable or if all variables are null then show default all records

What I have tried:

Declare @ESID int =null, @CenterID int =null, @CertificateID int =null, @ModuleID int =null, @CandidateID int =null

Declare @CandidateSessionStatus table(id int, [Status] nvarchar(50))

Declare @CandidateCertificateModuleStatus table(id int, [Status] nvarchar(50))

Insert into @CandidateSessionStatus([Status],id)
values('Scheduled','1'), ('Inprogress','2'), ('Cancelled','3'), ('Completed','4'), ('Absent','5') 

Insert into @CandidateCertificateModuleStatus([Status],id)
values('Pending','1'), ('Scheduled','2'), ('Failed','3'), ('Passed','4'), ('Cancelled','5'), ('Absent','6')

Select 
		c.ID,
		c.FirstNameEN + '  ' + c.LastNameEN as 'CandidateName',  
		c.Email, 
		c.ContactNumber, 
		c.UserName, 
		ct.NameEN as 'Center',
		cr.NameEN as 'Certificate', m.NameEN as 'Module', 
		es.SlotTime, cs.SessionToken,
		css.[Status] as 'CandidateSessionStatus', 
		ccms.[Status] as 'CandidateCertificateModuleStatus', 
		ccm.PercentageScore  
from CandidateSession cs 
inner join ExamSession es on cs.ExamSessionID = es.ID
inner join CandidateCertificate cc on cc.ID = cs.CandidateCertificateID
inner join Certification cr on cr.ID = cc.CertificationID
inner join Module m on m.ID = cs.ModuleID
inner join Candidate c on c.ID = cc.CandidateID
inner join Center ct on ct.ID = c.CenterID
inner join CandidateCertificateModule ccm on ccm.CandidateCertificateID = cc.ID and ccm.ModuleID = cs.ModuleID
inner join @CandidateCertificateModuleStatus ccms on ccms.id = ccm.StatusID
inner join @CandidateSessionStatus css on css.id = cs.StatusID
where COALESCE(es.ID = @ESID, cc.ID = @CertificateID,  m.ID = @ModuleID, ct.ID = @CenterID, c.ID = @CandidateID)--Error

can anyone please help me.

Thanks

Posted 4-Dec-16 2:51am

abdul subhan mohammed

Updated 4-Dec-16 22:39pm

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2016-12-04T04:24:00

Solution 1

Try

SQL

WHERE CASE 
       WHEN @ESCID IS NOT NULL THEN ed.ID = @ESCID
       WHEN @CertificateID IS NOT NULL THEN cc.ID = @CertificateID 
       WHEN @ModuleID IS NOT NULL THEN m.ID = @ModuleID
       WHEN @CenterID IS NOT NULL THEN ct.ID = @CenterID
       WHEN @CandidateID IS NOT NULL THEN c.ID = @CandidateID
       ELSE 1 == 1
       END

Posted 4-Dec-16 4:24am

OriginalGriff

Comments

abdul subhan mohammed 5-Dec-16 0:51am

we can't assign values in case stmt,
ed.ID = @ESCID --Error
--Incorrect syntax near WHEN

jamuro77 · Answer 2 · 2016-12-04T22:39:00

You need "optional params". Try this:

where
(@ESID is null or es.ID = @ESID) and
(@CertificateID is null or cc.ID = @CertificateID) and
(@ModuleID is null or m.ID = @ModuleID) and
(@CenterID is null or ct.ID = @CenterID) and
(@CandidateID is null or c.ID = @CandidateID)

But, if possible, try not use optional parameters in where fields pointing at primary keys. It is very bad regarding use of indexes.