Conditional Statements Inside A Where Clause

Question

I have a sproc that runs on many different servers. In the sproc I have a select statement that looks like the following.

select *
from my_table
where id = @my_id
   and status in (1,3)

However, for one of the databases I need the second part of that where clause to only be for statuses equal to 1. So essentially...

select *
from my_table
where id = @my_id
   and status in (1)

I really don't want to us an if db_name() = 'mydb' statement because the actual select statements are much longer and the code starts to look really inelegant at that point. Is there some way to make the second part of the where clause conditional on the db_name?

Thanks for your help.

Answer 1

It will be inelegant, really, because it's conditional.

So, if status IN (1, 3) means status = 1 or status = 3...

where id = @my_id
   and
       (
       status = 1
       or
       (db_name() <> 'mydb' and status = 3)
       )

Answer 2

Try this: (I think I got your logic right...) Cause you want it if the status is 1 for any database, but only want the status 3s for the databases that are not that single specified one...

   Select ...
   Where id = @my_id  
      And (status = 1 Or 
         (Db_Name() <> @DbName And status = 3))

Answer 3

How about something like this

select * 
from dbo.MyTable
where 1=1
and 
    (DB_NAME() = 'FirstDatabase' and Status in (1))
OR
    (DB_NAME() = 'SecondDatabase' and Status in (1, 3))

Answer 4

A little complex, but it'll do, if DB_NAME() works.

select * from my_table where id = @my_id and (status in (1) or (DB_NAME() <> 'mydb' AND status = 3))

Answer 5

You can use a case statement

select *
from    my_table
where   id = @my_id   
and  status = CASE 
         WHEN db_name() = 'mydb'
          THEN 1
         WHEN db_name() = 'other' 
          THEN 3
         ELSE -1
        END

Answer 6

Create a new table.

select my_table.*
from my_table
inner join my_valid_statuses on my_table.status = my_valid_statuses.status
where my_table.id = @my_id

Then make sure that my_valid_statuses is populated appropriately in each database.