How to use a view name stored in a field for an sql query ?

Question

I have a table with a view_name field (varchar(256)) and I would like to use that field in an sql query.

Example :

TABLE university_members

id | type | view_name | count

1 | professors | view_professors | 0

2 | students | view_students2 | 0

3 | staff | view_staff4 | 0

And I would like to update all rows with some aggregate calculated on the corresponding view (for instance ..SET count = SELECT count(*) FROM view_professors).

This is probably a newbie question, I'm guessing it's either obviously impossible or trivial. Comments on the design, i.e. the way one handle meta-data here (explicity storing DB object names as strings) would be appreciated. Although I have no control over that design (so I'll have to find out the answer anyway), I'm guessing it's not so clean although some external constraints dictated it so I would really appreciate the community's view on this for my personal benefit.

I use SQL Server 2005 but cross-platform answers are welcome.

Answer 1

To do this you would have to do it as a bit of dynamic SQL, something like this might work, obviously you would need to edit to actually match what you are trying to do.

DECLARE @ViewName VARCHAR(500)

SELECT @ViewName = view_name
FROM University_Members
WHERE Id = 1

DECLARE @SQL VARCHAR(MAX)

SET @SQL = '
UPDATE YOURTABLE
SET YOURVALUE = SELECT COUNT(*) FROM ' + @ViewName + '
WHERE yourCriteria = YourValue'

EXEC(@SQL)

Answer 2

The way I see it, you could generate SQL code in a VARCHAR(MAX) variable and then execute it using EXEC keyword. I don't know of any way to do it directly, as you tried.

Example:

DECLARE @SQL VARCHAR(MAX)
SET @SQL = ''
SELECT @SQL = @SQL + 'UPDATE university_members SET count = (SELECT COUNT(*) FROM ' + view_name + ') WHERE id = ' + id + CHAR(10) + CHAR(13) FROM university_members

EXEC @SQL

Warning! This code is not tested. It's just a hint...

Answer 3

Dynamic SQl is the only way to do this which is why this is a bad design choice. Please read the following article if you must be using dynamic SQl in order to protect your data. http://www.sommarskog.se/dynamic_sql.html

Answer 4

As HLGEM wrote, the fact that you're being forced to use dynamic SQL is a sign that there is a problem with the design itself. I'll also point out that storing an aggregate in a table like that is most likely another bad design choice.

If you need to determine a value at some point, then do that when you need it. Trying to keep a calculated value like that synchronized with your data is almost always fraught with problems - inaccuracy, extra overhead, etc.

There are very rarely situations where storing a value like that is necessary or gives an advantage and those are typically in very large data warehouses or systems with EXTREMELY high throughput. It's nothing that a school or university is likely to encounter.