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

Question

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!

Rather than a single procedure it would be best from a performance perspective if you create one procedure as your "driver" that has the IF conditions. Then inside the conditions you call separate procedures. The pattern you are describing is known as multiple execution paths. Gail Shaw has a great article on the topic here. sqlinthewild.co.za/index.php/2009/09/15/… — Sean Lange
– Sean Lange, Commented Apr 20, 2016 at 16:53
You just need to cast(getdate() as date) and change the then to begin and it should do what you need. — Martin Smith
– Martin Smith, Commented Apr 20, 2016 at 17:09
Thanks Martin, this worked! I've tried this a few times in the past, but embarassingly enough I must have just had the IF BEGIN END syntax wrong all this time! — Jay
– Jay, Commented Apr 20, 2016 at 17:26

Jande · Accepted Answer · 2016-04-20 18:22:55Z

1

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

answered Apr 20, 2016 at 18:22

Jande

1,7152 gold badges23 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jay Over a year ago

Yes this is what Martin suggested above, it worked, thank you!

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related