SQL服务器中的条件where子句 [英] Conditional where clause in SQL server

问题描述

大家好，



我想在sql server 2012中编写一个存储过程。



在where子句中，如果一个变量为null，则按另一个变量搜索或者如果所有变量都为null则显示默认所有记录



我尝试过：

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

推荐答案

试试

Try

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

您需要可选参数。试试这个：



其中

（@ESID为null或es.ID = @ESID）和

（@CertificateID为null或cc.ID = @CertificateID）和

（@ModuleID为null或m.ID = @ModuleID）和

（@CenterID为null或ct.ID = @CenterID）和

（@CandidateID为null或c.ID = @CandidateID）



但是，如果可能，尽量不要在指向主键的字段中使用可选参数。使用索引非常糟糕。

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.

这篇关于SQL服务器中的条件where子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！