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

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.

Conditional where clause in SQL server

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Conditional where clause in SQL server

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us