Click here to Skip to main content
15,070,111 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Something probably very simple, however I cannot get it.
I have a query like:
SQL
select * from Table t where t.id = 1


Now, I have a variable:
SQL
declare @type int = 0


Type can be 0 or 1 or whatever else.
I am trying to do something like this:

select everything from Table where id equals 1 and (if type equals 0 then name should be 'something' else the condition is true)

In LINQ I would do it like this:
C#
queryable.Where(x => x.id == 1 && (type != 0 || x.name == "something"))


What I have tried:

So this is what I came up with (which of course is wrong):

SQL
select * Table t where t.id = 1 and (case when @type = 0 then t.name = 'something' else 1=1 end)


Any help is greatly appreciated.
Thanks

P.S. In addition to that, I am trying no to use 'OR' because for some reason SQL server is very slow on 'ORs' and my query is already very big (300 lines) so performance is very important.
Posted
Updated 9-Jun-21 21:22pm
v2
Comments
Maciej Los 10-Jun-21 2:09am
   
Like this?
select * Table t where t.id = 1 and (@type <> 0 OR t.name = 'something')
csrss 10-Jun-21 2:17am
   
I am just considered about using 'OR' in this solution. As I have mentioned, OR is very slow.
Maciej Los 10-Jun-21 2:51am
   
Logical operator is NOT causing slow execution of query.

Try:
SQL
... WHERE t.ID = 1 AND ((@Type = 0 AND t.Name = 'something') OR @Type <> 0)
   
v2
Comments
csrss 10-Jun-21 2:24am
   
Is there a solution without using 'OR' maybe?
Or isn't always slow, it depends.

If the conditions are on the same table and it's also indexed an or will not be slow.
But if the OR conditions are on different tables in a join, the server will have to join all rows and filter the result, which can be extremely slow.

In this case one of the conditions isn't even on a column but on a variable.

One common workaround is to convert the query into two queries with a union between them. This allows the server to use indexes and to filter on the fly.

SQL
SELECT  *
FROM    Table t
WHERE   t.id = 1
    AND @type <> 0
UNION
SELECT  *
FROM    Table t
WHERE   t.id = 1
    AND t.name = 'something')
Whether this works or not depends on how the actual query looks like
   
v2
Comments
csrss 10-Jun-21 4:15am
   
Thanks for the explanation! I'll look into this later.

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