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 · 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

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

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

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

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

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