Re: STORED PROCEDURE - passing table name as a parameter

Steve (sjc4914@xxxxxxxxx) writes:
> The double-bad news is that table are created or dropped frequently, at
> least during the initial deployment

Congratulations. :-(

> Can you point me to a good tutorial or book on creating dynamic views?
> That would solve another data access problem that is looming on the
> horizon -- I can feel it coming. The view could be regenerated daily
> during off-hours, and that would be sufficient.

"Dynamic views" are not dynamic in the true sense, they are just dynamically
generated. All you need is means to identify the tables in question. For
instance say all these tables opens with the string 'lb1table'. Then you
would set up a cursor over

SELECT name FROM sysobjects WHERE name LIKE 'lbltable%'

And then foreach row add to the view definition

SELECT @viewdef = @viewdef +
'SELECT tblname = ''' + @name + ''', * FROM ' + @name +
' UNION ALL'

At the end of the loop you need get rid of that last UNION ALL. Eventually
you would say EXEC(@viewdef).

Here I used T-SQL, but you could just as well write a program in VB, C,
Perl whatever that did the same thing. The problem with doing this in
T-SQL, is that you get problems if the view definition does not fit into
a varchar(8000).

The tricky part may be to identify which tables to include. Possibly you
will need to query other system tables, for instance syscolumns.


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

Relevant Pages

  • Re: Cant create new query
    ... Books Online is a pretty good start; most of the books I have for T-SQL are ... the visual designers) will teach you all kinds of bad habits. ... tables and then copy-paste into SQL Server ...
    (microsoft.public.sqlserver.tools)
  • Re: Dinamyc Function
    ... This is T-SQL, this is not C#. ... virtue in T-SQL, it is not as big virtue as in an traditional programming ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Flattening Parent Child, an issue, please help
    ... AND t2.WBS_LEVEL = 2 LEFT OUTER JOIN ... I am using SQL Server 2000 with SP4. ... For information about dyamic SQL from T-SQL see ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Scripting Databases
    ... Basically, if I want to script a number of tables, I can right hand ... Again, T-SQL ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Update or Delete querys with Joins
    ... This is were I was missing the boat Steve. ... update multiple columns in the ANSI with the same right-side of query. ... that time as Microsoft deems it suitable for inclusion into T-SQL. ... > but SQL Server doesn't implement it. ...
    (microsoft.public.sqlserver.programming)