pcasey (8/24/2012)
Dozens of our tables are set up where the table names are identical except for an ID number at the end. I am often called upon to write queries for other employees to use, and I would like to simplify things for them by putting that ID number in a variable at the top of the query. Then, the user would only need to set the variable and click Execute....
This is a very non-Relational design and you are going to have no end of trouble with it. Although the previous replies contain many good suggestions, it needs to be said: The best possible solution would be to fix the design so that most of these tables with identical design are in fact the same table. Then you can add some kind of tenant/owner column to keep the rows separate, when you need to, but other than that, just use the same queries.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]