title: Can I dynamically call a stored procedure from a view?
body: <p>Specifically, can I call a proc from the <em>current database</em> in a view.  I already know about the <a href="https://stackoverflow.com/questions/349979/execute-a-sql-stored-proc-from-a-sql-view">openrowset hack</a>, so this works, for example:</p>
  
  <pre><code>create view MyView as
      select *
      from openrowset (
          'sqloledb',
          'server=(local);trusted_connection=yes;',
          'exec MyDatabase.dbo.MyStoredProcedure' -- Works fine
      )
  </code></pre>
  
  <p>But I'd like to be able to call the proc from the current DB without hard-coding the name like so:</p>
  
  <pre><code>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
      )
  </code></pre>
  
  <p>This doesn't work, unfortunately, as openrowset expects literal strings, rather than variables of any sort.</p>
  
  <p>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).</p>