Posted by Erland Sommarskog on 11/30/05 11:50

Steve (sjc4914@yahoo.com) writes:
> This is a "commercial" database solution purchased to record and report
> environmental data. I don't like the design for all the reasons already
> posted, and more. Though I had nothing to do with the selection,
> unfortunately, I am charged with completing a task, so like it or not, it
> is on my plate.
> Terry Kreft's idea looks workable, but not optimum. It would seem like >
> there would be a way to get around pasting strings together and then
> executing the string. I was wondering if there was a way to use a
> subquery to return a table reference. It is a rather obtuse way of
> getting there, and I haven't investigated it yet. Any alternate
> solutions anyone can think of?

David's suggestion of a view is a possible way, although I don't think it
can be an indexed view, as indexed views can not include the UNION keyword.

The view definition would look like:

SELECT tablename = 'tbl1', * FROM tbl1
UNION ALL
SELECT tablename = 'tbl2', * FROM tbl2
...

When you query the view as:

SELECT ...
FROM yourview
WHERE tablename= 'tbl2'
AND ...

I would expect SQL Server to be smart to not access the other tables.
They would appear in the query plan, but a startup expression would prevent
any access to the tables.

The other option beside dynamic SQL would be to write a small macro
processor that reads a common stub, and the substitutes the table name,
so that you have one procedure per table.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]