How to call Stored Procedure in a View?

Question

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

Answer 1

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.

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 

Answer 2

exec sp_addlinkedserver          @server = 'local',         @srvproduct = '',         @provider='SQLNCLI',         @datasrc = @@SERVERNAME go  create view ViewTest as select * from openquery(local, 'sp_who') go  select * from ViewTest go