Geek asks and answers

Can I dynamically call a stored procedure from a view?

In particular, can I call proc from the current database in the view. I already know about 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 would like to be able to call proc from the current database without hard-coding the name like this:

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 does not work, unfortunately, since openrowset expects literal strings, not variables of any type.

Regardless of security and performance considerations, is there a workaround? This will make servicing an outdated system much more tolerant, since the proc that invokes this view connects to another database depending on the environment (dev, test, prod).

+3
1

, SQL . , "" / . , (/):

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'

SQL , , , , , .

DROP SYNONYM dbo.MyView;
GO
CREATE SYNONYM dbo.MyView FOR dbo.devMyView;

, dbo.MyView, dev. , / .

+2

More articles:


All Articles