December 15, 2017 at 5:07 am
Hi,
Is it possible to create a view that can have a database name parameter?
I am creating a view on a database that points to another UAT database but would also like be able to switch it to the live version of the database.
The view references many tables and uses the [DATABASE].[DBO].
convention.
My question is whether I can make the [DATABASE] a parameter.
The reason is to keep one version of the view. Also, I can't create the view on UAT or live.
I hope this question isn't too confusing?
Thanks,
Eamon
December 15, 2017 at 5:14 am
Eamon
No, views don't have parameters. A stored procedure would work, although you'd need to use dynamic SQL. Make sure you understand, and guard against, SQL injection if you do that. Or you could put the view in each of the databases you need to run it against, and use three-part naming when calling it: SELECT col1, col2 FROM MyDatabase.dbo.MyView.
John
December 15, 2017 at 5:42 am
Hi John,
this is what I thought (but I figure I would ask in case a new piece of SQL functionality has later versions to allow otherwise).
Many thanks,
Eamon
December 19, 2017 at 8:32 am
If you are running queries against a remote database then I would suggest that you set up the tables/views in the remote database as Synonyms and reference these in your view/sproc. When you move environments the script for the synonyms is part of the DEV-OPS
note that it won't work for remote functions
December 19, 2017 at 12:48 pm
That's a very interesting answer and interesting approach.
I just might give that a go.
Thanks alot Aaron.
Regards,
Eamon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply