Tutorial :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?


Solution: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  


Solution: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  


Solution: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  


Solution:4

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


Solution: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


Solution:6

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.


Solution:7

CREATE VIEW [dbo].[dimMeasure]  AS    SELECT * FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;'                           , 'exec ceaw.dbo.sp_dimMeasure2')    GO  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »