[sql-server] How to call Stored Procedure in a View?

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

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

Easiest solution that I might have found is to create a table from the data you get from the SP. Then create a view from that:

Insert this at the last step when selecting data from the SP. SELECT * into table1 FROM #Temp

create view vw_view1 as select * from table1

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.

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

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

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