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

User · Accepted Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql-server

Examples related to tsql

Examples related to database-design