Re: STORED PROCEDURE - passing table name as a parameter
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Wed, 30 Nov 2005 09:50:18 +0000 (UTC)
Steve (sjc4914@xxxxxxxxx) 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@xxxxxxxxxxxxx
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
.
- References:
- STORED PROCEDURE - passing table name as a parameter
- From: Steve
- Re: STORED PROCEDURE - passing table name as a parameter
- From: David Portas
- Re: STORED PROCEDURE - passing table name as a parameter
- From: Steve
- STORED PROCEDURE - passing table name as a parameter
- Prev by Date: Re: Problem with spid by sp_who
- Next by Date: Re: Passing DateDiff rather than Actual Dates as Parameters
- Previous by thread: Re: STORED PROCEDURE - passing table name as a parameter
- Next by thread: Passing DateDiff rather than Actual Dates as Parameters
- Index(es):
Relevant Pages
|
