Dynamic View

Dear All,
Hoping you might be able to help me out with a SQL issue.
Want to have a view that contains a join of two tables:
SELECT dbo.VEC_CASE.*, dbo.VEC_MI.*
FROM dbo.VEC_MI INNER JOIN
dbo.VEC_CASE ON dbo.VEC_MI.ID = dbo.VEC_CASE.ID
Problem is that the ID column exists in both tables (and contains the
same value in both) so it wont work as a view, even though it runs fine
as a query.
We keep on adding columns to the tables, and am sick of having to
remember to redefine the view each time and specify all the columns we
want (real one is much more complex than this one).
Can you suggest a way of creating a dynamic view which returns all the
columns, but only one instance of the ID column?
Have tried -
select column_name + ', '
from information_schema.columns where table_name = 'vec_mi'
and column_name <> 'id'
Which will give me a list of all the columns except ID, but the results
are in the form of a recordset. When I try to use this in the view:
SELECT
dbo.VEC_CASE.*,
(select column_name + ', '
from information_schema.columns where table_name = 'vec_mi'
and column_name <> 'id' )
FROM dbo.VEC_MI INNER JOIN
dbo.VEC_CASE ON dbo.VEC_MI.ID = dbo.VEC_CASE.ID
It complains that the subquery returns more than one value.
Is there a way to convert the contents of a recordset into a single
string?
Also tried to create a Stored Procedure / Function to return the
results of the subquery, but cant get the view to recognise the name of
the stored procedure - thinks it is a column.
Also, this whole approach would mean that the design of the view might
change upon execution and so the view might not allow me to do this in
any event.
All assistance gratefully accepted.
Thanks,
Martinjumpa (martin@.jumpa.co.uk) writes:
> Hoping you might be able to help me out with a SQL issue.
> Want to have a view that contains a join of two tables:
> SELECT dbo.VEC_CASE.*, dbo.VEC_MI.*
> FROM dbo.VEC_MI INNER JOIN
> dbo.VEC_CASE ON dbo.VEC_MI.ID = dbo.VEC_CASE.ID
> Problem is that the ID column exists in both tables (and contains the
> same value in both) so it wont work as a view, even though it runs fine
> as a query.
> We keep on adding columns to the tables, and am sick of having to
> remember to redefine the view each time and specify all the columns we
> want (real one is much more complex than this one).
> Can you suggest a way of creating a dynamic view which returns all the
> columns, but only one instance of the ID column?
Keep on adding the columns *that you need* to the view. SELECT * is
generally frowned upon in production code. Say that in five years from
now, someone is looking at the tables and says "hm, I wonder if that
column foo is really used for something real". Well, if SELECT statements
and views only lists columns that are actually used for something, it
can be quite easy to find out, at least if all access is through stored
procedure. But with the SELECT statement like the above, you need to
dive into the client code.
No big deal? There may be a cost for maintaining the value in foo,
and one may consider a redesign that would be a lot easier if we
got forget about foo. But if it's impossible to tell whether foo is
in use, it will have to stay.
And so the system grows, acquiring a bigger and bigger backpack of
legacy, making the system difficult to maintain and evolve.
So keep on adding the columns that are really needed in the view, and
no others.
And, no, there is no "SELECT * - thatcolmn".
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On 6 Jun 2006 06:37:44 -0700, jumpa wrote:

>Dear All,
>Hoping you might be able to help me out with a SQL issue.
(snip)
>All assistance gratefully accepted.
Hi Martin,
In addiition to Erland's reply - both Querty Analyzer and SQL Server
Management Studio allow you to quickly copy alll column names of a table
to a query window by using drag & drop from the object explorer. After
that, you'll just have to remove the duplicates and the unneeded
columns, add prefixes, and you're done.
Hugo Kornelis, SQL Server MVP|||Thanks for the responses guys. This is the first time i've ever used a
discussion group for help with development, definitely not the last.
Agreed, will just have to continue with the manual route.
Incidentally, have since written a stored procedure which drops and
recreates the view with all columns.
Many thanks,
Martin