C#•3y ago

❔ SQL Server | Can i use 'Case When' in Where Clause?

i ususally do it by create the query into the string like this

set @sql = 'select * from person p .....'
set @where = ' where xxx = xxx'

IF @role <> 'ALL'
BEGIN
  SET @where= @where + ' AND p.role IN ('''+@ProductId+''')'
END

SET @query = @sql +  @where
EXECUTE(@query)

set @sql = 'select * from person p .....'
set @where = ' where xxx = xxx'

IF @role <> 'ALL'
BEGIN
  SET @where= @where + ' AND p.role IN ('''+@ProductId+''')'
END

SET @query = @sql +  @where
EXECUTE(@query)

but it realyyyy hard to debug if the query is very long, so anyone know the proper way to do it?

6 Replies

Unknown User•3y ago

Message Not Public

FroH.LVT•3y ago

If you don't need a complex query plus a special reason then just use ORM. EFCore to go I would do something like this with SQL in your case:

Declare @ProductFilterValue
Declare @OtherFilterValue1
....
Declare @OtherFilterValueN

select * 
from Person
where 1 = 1 
And (product = @ProductFilterValue OR @ProductFilterValue = defaultValue)
And (Column1 = @OtherFilterValue1 OR @OtherFilterValue1 = defaultValue)
... 
And (ColumnN = @OtherFilterValueN OR @OtherFilterValueN = defaultValue)

Declare @ProductFilterValue
Declare @OtherFilterValue1
....
Declare @OtherFilterValueN

select * 
from Person
where 1 = 1 
And (product = @ProductFilterValue OR @ProductFilterValue = defaultValue)
And (Column1 = @OtherFilterValue1 OR @OtherFilterValue1 = defaultValue)
... 
And (ColumnN = @OtherFilterValueN OR @OtherFilterValueN = defaultValue)

Btw, your post name is pretty confusing

FROP•3y ago

i want to put it into sp so probably not using orm i see, my brain still processing but i bit understand ,so if it cant find the value it will goes to second operatos and because the statement is inside brackets it will not bother the other statment outside it (product = @ProductFilterValue OR @ProductFilterValue is not null) hahaha sry my grammar really bad, but ty a lot brow

Yawnder•3y ago

The answer: Yes

FroH.LVT•3y ago

Good luck. I don't remember if the OR condition statement's order would affect performance. It's better to switch

(product = @ProductFilterValue OR @ProductFilterValue = defaultValue)

(product = @ProductFilterValue OR @ProductFilterValue = defaultValue)

(@ProductFilterValue = defaultValue OR product = @ProductFilterValue )

(@ProductFilterValue = defaultValue OR product = @ProductFilterValue )

Accord•3y ago

Was this issue resolved? If so, run /close - otherwise I will mark this as stale and this post will be archived until there is new activity.

Gaming

Programming

❔ SQL Server | Can i use 'Case When' in Where Clause?