Search code examples
ssmswhere-clausesql-server-2016

Use table values in a where clause

I am working on a stored procedure that will have a pretty big WHERE clause. In an effort to simplify it, I am trying to utilize a SQL table that I can use to use in my code.

Prior to thinking of using the table, my query would have looked like this:

Select * from dbo.SomeTable 
where (Val1 = 'ME' and val2 = 17 and val3 = 'Apple') 
    or (Val1 = 'GE' and Val2= 7 and Val3 = 'Google')

I would like to use this table below to be able to dynamically accomplish a similar thing

Table1

Val1        Val2       Val3
ME          17         Apple
GE          7          Google

Is there any good way to do that?

Thanks.

Solution

  • Unless you have oversimplified your intentions you just need to join your tables

    Select st.*
from dbo.SomeTable st
join Table1 t on t.val1=st.val1 and t.val2=st.val2 and t.val3=st.val3

    alternatively you can use exists

    select * 
from dbo.SomeTable st
where exists (select * from Table1 t where t.val1=st.val1 and t.val2=st.val2 and t.val3=st.val3)