Can I dynamically call a stored procedure from a view?
No, you can't use dynamic SQL in a view. You could just create three views if there are only three different "environments", and/or use synonyms depending on the environment. So for example, you could have three views (pseudo/trimmed):
create view dbo.devMyView as
select * ... 'exec Dev.dbo.MyStoredProcedure'
go
create view dbo.testMyView as
select * ... 'exec Test.dbo.MyStoredProcedure'
go
create view dbo.prodMyView as
select * ... 'exec Prod.dbo.MyStoredProcedure'
Then you can use dynamic SQL in your code to specify which view you want, or you can drop and create a synonym when you want to simulate each environment, e.g.
DROP SYNONYM dbo.MyView;
GO
CREATE SYNONYM dbo.MyView FOR dbo.devMyView;
Now the code that references dbo.MyView will ultimately call the stored procedure in the dev database. The downside of this is that only one synonym can be active/redirecting at any given time.
Comments
-
Tim Lehner almost 2 years
Specifically, can I call a proc from the current database in a view. I already know about the openrowset hack, so this works, for example:
create view MyView as select * from openrowset ( 'sqloledb', 'server=(local);trusted_connection=yes;', 'exec MyDatabase.dbo.MyStoredProcedure' -- Works fine )
But I'd like to be able to call the proc from the current DB without hard-coding the name like so:
create view MyView as select * from openrowset ( 'sqloledb', 'server=(local);trusted_connection=yes;', 'exec ' + db_name() + '.dbo.MyStoredProcedure' -- Don't want to hard-code DB name )
This doesn't work, unfortunately, as openrowset expects literal strings, rather than variables of any sort.
Regardless of security and performance considerations, is there a workaround? It would make the maintenance of a legacy system much more bearable, as the proc that this view will call connects to a different database depending upon environment (dev, test, prod).