CODEWITHSUNDEEP
sqlsql-serverconditional-statements
Andrew Cooper
Andrew Cooper

Reputation: 747

Conditional Statements Inside A Where Clause

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.

Upvotes: 0

Views: 3625

Answers (6)

Broam
Broam

Reputation: 4648

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))

Upvotes: 0

Christian Hayter
Christian Hayter

Reputation: 31071

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.

Upvotes: 1

gbn
gbn

Reputation: 432662

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)
       )

Upvotes: 1

Adriaan Stander
Adriaan Stander

Reputation: 166606

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

Upvotes: 2

Raj More
Raj More

Reputation: 48048

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))

Upvotes: 0

Charles Bretana
Charles Bretana

Reputation: 146603

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))

Upvotes: 0

Related Questions