Dynamic SQL with variables inside a view (SQL Server)

Hello I'm essentially trying to do this inside a new view window in SQL Server 2008:

declare @var = (select 开发者_JS百科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