How to use view name stored in field for sql query?
I have a table with a view_name field (varchar (256)) and I would like to use that field in my sql query.
Example:
TABLE university_members
id | type | view_name | amount
1 | professors | Views | 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 example ..SET count = SELECT count(*) FROM view_professors
).
This is probably a newbie question, I guess it is either obviously impossible or trivial. Design notes, i.e. the way to use single descriptor metadata here (explication storing DB object names as strings) would be appreciated. While I have no control over this design (so I'll have to figure out the answer anyway), I guess it's not that clean, although some external constraints dictated it, so I'd really appreciate community input on this for my personal benefit ...
I'm using SQL Server 2005, but cross-platform answers are appreciated.
source to share
To do this you will need to do it as a bit of dynamic SQL, something like this might work, obviously you will 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)
source to share
As I see it, you can generate the SQL in VARCHAR (MAX) and then execute it using the EXEC keyword. I don't know how to do this, how have 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
Attention! This code is untested. This is just a hint ...
source to share
Dynamic SQl is the only way to do this, which is why it is a poor design choice. Please read the following article if you must use dynamic SQl to protect your data. http://www.sommarskog.se/dynamic_sql.html
source to share
As HLGEM wrote, the fact that you are forced to use dynamic SQL is a sign that the problem is with the design itself. I will also point out that keeping an aggregate in a table like this is most likely another bad design choice.
If you need to define a value at some point, then do it when you need it. Trying to store a calculated value like the one that syncs with your data almost always comes with problems - inaccuracy, additional overhead, etc.
There are rarely situations where storing this value is necessary or beneficial, and usually very large data warehouses or systems with EXTREMELY high throughput. This is nothing that can be found in school or university.
source to share