Dynamic SQL with variables inside a view (SQL Server)

Question

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.

Answer 1

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?