Click here to Skip to main content
14,980,591 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Updated 4-Dec-16 22:39pm

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
   
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
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.
   

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900