union all on views based on table

i have a database table with a list of sql views

i want to create a new view or stored procedure that based on which views开发者_JS百科 are in that table will return those views unioned like this

SELECT     ALINE1, HOME, EMAIL, EXPIRE, EDATE7, type
    FROM         dbo.campaign_membership_30
    UNION ALL
    SELECT     ALINE1, HOME, EMAIL, EXPIRE, EDATE7, type
    FROM         dbo.campaign_membership_30n
    UNION ALL
    SELECT     ALINE1, HOME, EMAIL, EXPIRE, EDATE7, type
    FROM         dbo.campaign_membership_60n
    UNION ALL
    SELECT     ALINE1, HOME, EMAIL, EXPIRE, EDATE7, type
    FROM         dbo.campaign_membership_today
    UNION ALL
    SELECT     ALINE1, BOOKNO, EMAIL, DEPART, DEP7, type
    FROM         dbo.depart_151days
    UNION ALL
    SELECT     ALINE1, BOOKNO, EMAIL, DEPART, DEP7, type
    FROM         dbo.depart_90Days

I think you're asking for a way to have a table drive which views are part of the query. In other words, if your table had the following rows:

dbo.View1
dbo.View2
dbo.View3

Your query would return the union of of those three views; correct?

A caveat: I highly question the underlying relational structures that require you to do this. A better data model would probably render this unnecessary. But I'm assuming you don't have the ability to change model, so try this:

Assume the three views listed above are in a field called "ViewName" on a table called "ViewsToReturn." Construct a query that first unions all of the views, then filters it according to the names of the views in ViewsToReturn. Like this:

SELECT * FROM
    (SELECT Field1, Field2, 'dbo.View1' AS ViewName FROM dbo.View1
    UNION
    SELECT Field1, Field2, 'dbo.View2' AS ViewName FROM dbo.View2
    UNION
    SELECT Field1, Field2, 'dbo.View3' AS ViewName FROM dbo.View3
    UNION
    SELECT Field1, Field2, 'dbo.View4' AS ViewName FROM dbo.View4) AllRecords
WHERE AllRecords.TableName IN
    SELECT ViewName FROM ViewsToReturn

This solution is neither elegant nor performant, but should do the trick if I understand your question correctly.

Good luck!

There are a lot of major caveats here.

First, using dynamic SQL is often dangerous. It can open up your database to SQL injection attacks. If you don't understand what these are then you need to do a lot of educating of yourself before I would suggest that you use dynamic SQL.

Second, this kind of a pattern (storing tables and columns in a table from which to generate SQL) is a really bad pattern. There are many problems with it. I can't suggest alternative solutions though without knowing more about your application/problem space.

That said, the following is a simplistic version of how it might work in SQL Server 2005. This does not include error handling, etc.

DECLARE @sql VARCHAR(MAX)  -- NVARCHAR if you use unicode characters in table names

SELECT
    @sql = dbo.Concatenated_Rows('SELECT col1, col2 FROM ' + MT.table_name + ' UNION ALL ')
FROM
    dbo.My_Table MT

-- Remove the extra UNION ALL
SELECT @sql = SUBSTRING(@sql, 1, LEN(@sql) - 11)

EXEC(@sql)

The function Concatenated_Rows would have to be written. You could alternatively use a cursor or maybe even FOR XML to create the concatenated string. Here's a link that does a good job explaining many of the possible methods and does a good job of comparing them.

Again, there are probably better solutions out there then going this route, especially if this is more than a one-off task.