CASE to determine if certain WHERE clause should be used or not?
-
declare @CandidateID int
set @CandidateID = 0
SELECT * FROM tblCandidates
WHERE
CASE @CandidateID
WHEN 0 THEN 1
ELSE ID_Kandidat = @CandidateID
END
The CASE does not work. I found examples using CASE to determine which of some criterias to use in a WHERE clause but not if a clause is used.
-
Try this query:
select
*
from
dbo.tblCandidates
where
(@Candidate = 0) or
(ID_Kandidat = @CandidateID)
-
Ah, I see. Very cool. Thanks a lot!!
-
SELECT * FROM tblCandidates
WHERE 1 = CASE WHEN @CandidateId = 0 THEN 1
WHEN ID_Kandidat = @CandidateID THEN 1
ELSE 0
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
"...The CASE does not work...."
Hi Michael,
What doesn't work is that the conditional expressions in T-SQL don't function like C# or VB, or just about any other language.
They require an operator, and won't work with a boolean expression. There are exceptions, like EXISTS().
So for example, you can't code:
WHILE 1
You have to code:
WHILE 1=1
jg
-
Ok, thats good to know. Thank you!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply