Jay Jay - 7 months ago 22
SQL Question

SQL - run query against different table (dynamic table) based on parameter

I basically have a query with a

@date
parameter, and depending on that
@date
I want to run a query against the proper table. If it matters, the tables are the same structure but on 2 different servers however they are linked so running from either is not an issue. So I'm looking to:

IF @date = getdate() THEN
SELECT * FROM server1.db1..MyTable
ELSE
SELECT * FROM server2.db2..MyTable_history WHERE date = @date
END


I'm pretty sure this can be done with dynamic sql, but the query is a little hairy so I would like to avoid that if possible. I was hoping to just be able to do something very clean like a big IF ELSE statement - run one query or the other.

Any help would be appreciated. Also I'm using SQL Server 2008 R2

Thank you!

Answer

Try this:

IF @date = CAST(getdate() as date)
BEGIN 
SELECT * FROM server1.db1..MyTable
ELSE
SELECT * FROM server2.db2..MyTable_history WHERE date = @date
END