在视图中执行存储过程？

Is it possible to execute a stored procedure in a view?

是否可以在视图中执行存储过程?

I.e.

CREATE VIEW [dbo].[v_ReportInvoiceClientsThisMonth] AS EXEC [dbo].[GetInvoiceClients] @startDate = '2009-03-01', @endDate = '2009-04-01'

创建视图[dbo]。[v_ReportInvoiceClientsThisMonth] AS EXEC [dbo]。[GetInvoiceClients] @startDate ='2009-03-01',@ endDate ='2009-04-01'

(doesn't work)

The reason I need this is that I need a way to access the SP in Excel (in an idiot safe way, i.e. without VBA).

我需要这个的原因是我需要一种方法来访问Excel中的SP(以白痴安全的方式,即没有VBA)。

1 个解决方案

#1

You can do this with a view over a table-valued function. This looks something like:

您可以通过表值函数的视图来执行此操作。这看起来像:

-- === Table-Valued function ====================================
--
create function fn_foo (
       @Date datetime

) returns @ResultSet table (
        DateKey           datetime
       ,DisplayDate       varchar (20)
) as
    insert @ResultSet (
           DateKey
          ,DisplayDate
    )
    select DateKey      -- Just pretend there's something to select
          ,DisplayDate  -- behind the scenes
      from ods.Dates
     where DateKey <= @Date
    return
go


-- === View ============================================
-- 
create view vw_foo (
       DateKey
      ,DisplayDate
) as
select DateKey
      ,DisplayDate
  from fn_foo ('2009-04-31')
go

There are a few caveats:

有几点需要注意:

There are some limits to what you can do with functions. In particular there is something of an impedance mismatch between stored procedure code and function code so you typically can't use a function to wrap around a stored procedure for the purposes of doing this sort of thing.

你可以用功能做一些限制。特别是存储过程代码和函数代码之间存在阻抗不匹配的问题,因此您通常不能使用函数来包装存储过程以执行此类操作。
The first point means you will probably have to re-cast your stored procedure as a table-valued function.

第一点意味着您可能必须将存储过程重新强制转换为表值函数。

#1