Hello I'm essentially trying to do this inside a new view window in SQL Server 2008:
declare @var = (select db from databases);
exec ('select name from ' + @var ' + .dbo.Names);
This view actually runs in SQL Server but I cannot save it (it gives me an error), I could potentially just create a table returning function, do all of this same stuff in it and return the table and create a view that basically takes everything from that table but I was unsure of performance hits that could occur from doing this. Any suggestions would be greatly appreciated! Thanks.
Solution:
I just ended up having it drop the old view and recreate a new view (using dynamic sql) in a Stored Procedure. When that value is changed I will just call the SP which will update the views to point to the correct databases. Thanks for all the help guys, knowing what can't be done stopped me from trying those methods.
View's cannot accept parameters. A table valued function IS the solution. But you have to at least know the table and result set that is going to come out the other end. If your passing the table to be queried as a parameter how do you know the structure of the resulting data set?
You can easily fake an internal variable in your view using CTE. You can test-run it in your version of SQL Server.
CREATE VIEW vwImportant_Users AS
WITH params AS (
SELECT
varType='%Admin%',
varMinStatus=1)
SELECT status, name
FROM sys.sysusers, params
WHERE status > varMinStatus OR name LIKE varType
SELECT * FROM vwImportant_Users
yielding output:
status name
12 dbo
0 db_accessadmin
0 db_securityadmin
0 db_ddladmin
also via JOIN
WITH params AS ( SELECT varType='%Admin%', varMinStatus=1)
SELECT status, name
FROM sys.sysusers INNER JOIN params ON 1=1
WHERE status > varMinStatus OR name LIKE varType
also via CROSS APPLY
WITH params AS ( SELECT varType='%Admin%', varMinStatus=1)
SELECT status, name
FROM sys.sysusers CROSS APPLY params
WHERE status > varMinStatus OR name LIKE varType
