Posted by Steve Jorgensen on 11/02/60 11:33

On Wed, 30 Nov 2005 09:50:18 +0000 (UTC), Erland Sommarskog
<esquel@sommarskog.se> wrote:

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

I can't tell if the OP is dealing with a fixed set of tables, or if new tables
are bring continuously created. If the former, then I guess the view would
work. If the later, then it would not.

[Back to original message]