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

If you are using Sql Server 2005 you can use table valued functions. You can call these directly and pass paramters, whilst treating them as if they were tables.

For more info check out Table-Valued User-Defined Functions

Answer 3

I was able to call stored procedure in a view (SQL Server 2005).

CREATE FUNCTION [dbo].[dimMeasure] 
   RETURNS  TABLE  AS

    (
     SELECT * FROM OPENROWSET('SQLNCLI', 'Server=localhost; Trusted_Connection=yes;', 'exec ceaw.dbo.sp_dimMeasure2')
    )
RETURN
GO

Inside stored procedure we need to set:

set nocount on
SET FMTONLY OFF

CREATE VIEW [dbo].[dimMeasure]
AS

SELECT * FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;', 'exec ceaw.dbo.sp_dimMeasure2')

GO

Answer 4

create view sampleView as 
select field1, field2, ... 
from dbo.MyTableValueFunction

Note that even if your MyTableValueFunction doesn't accept any parameters, you still need to include parentheses after it, i.e.:

... from dbo.MyTableValueFunction()

Without the parentheses, you'll get an "Invalid object name" error.

Answer 5

You would have to script the View like below. You would essentially write the results of your proc to a table var or temp table, then select into the view.

Edit - If you can change your stored procedure to a Table Value function, it would eliminate the step of selecting to a temp table.

**Edit 2 ** - Comments are correct that a sproc cannot be read into a view like I suggested. Instead, convert your proc to a table-value function as mentioned in other posts and select from that:

create view sampleView
as select field1, field2, ... 
from dbo.MyTableValueFunction

I apologize for the confusion

Answer 6

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