如何在视图中调用存储过程？ [英] How to call Stored Procedure in a View?

问题描述

如何调用在View中返回数据的存储过程？这是甚么可能吗？

How would I call a Stored Procedure that returns data in a View? Is this even possible?

推荐答案

SQL Server中不允许此构造。内联表值函数可以作为参数化视图执行，但仍然不允许调用这样的SP。

This construction is not allowed in SQL Server. An inline table-valued function can perform as a parameterized view, but is still not allowed to call an SP like this.

以下是使用SP和内联的一些示例TVF可互换 - 你会看到TVF更灵活（它基本上比一个视图更像一个功能），所以可以使用内联TVF，可以更可重复使用：

Here's some examples of using an SP and an inline TVF interchangeably - you'll see that the TVF is more flexible (it's basically more like a view than a function), so where an inline TVF can be used, they can be more re-eusable:

CREATE TABLE dbo.so916784 (
    num int
)
GO

INSERT INTO dbo.so916784 VALUES (0)
INSERT INTO dbo.so916784 VALUES (1)
INSERT INTO dbo.so916784 VALUES (2)
INSERT INTO dbo.so916784 VALUES (3)
INSERT INTO dbo.so916784 VALUES (4)
INSERT INTO dbo.so916784 VALUES (5)
INSERT INTO dbo.so916784 VALUES (6)
INSERT INTO dbo.so916784 VALUES (7)
INSERT INTO dbo.so916784 VALUES (8)
INSERT INTO dbo.so916784 VALUES (9)
GO

CREATE PROCEDURE dbo.usp_so916784 @mod AS int
AS 
BEGIN
    SELECT  *
    FROM    dbo.so916784
    WHERE   num % @mod = 0
END
GO

CREATE FUNCTION dbo.tvf_so916784 (@mod AS int)
RETURNS TABLE
    AS
RETURN
    (
     SELECT *
     FROM   dbo.so916784
     WHERE  num % @mod = 0
    )
GO    

EXEC dbo.usp_so916784 3
EXEC dbo.usp_so916784 4

SELECT * FROM dbo.tvf_so916784(3)    
SELECT * FROM dbo.tvf_so916784(4)

DROP FUNCTION dbo.tvf_so916784
DROP PROCEDURE dbo.usp_so916784
DROP TABLE dbo.so916784

这篇关于如何在视图中调用存储过程？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！