TSQL - How To Execute Stored Procedure In View

There could be scenarios where you want to use a Stored Procedure in view and Join the result set returned by a Stored procedure with some other tables/views.

Step 1:
Let's create a table that we want to use for this example

USE TestDB

go

CREATE TABLE dbo.Customer
  (
     Id    INT,
     FName VARCHAR(50),
     LName VARCHAR(50)
  )GO
INSERT INTO dbo.Customer

VALUES      (1,
             'Aamir',
             'Shahzad'),
            (2,
             'Robert',
             'Ladson'),
            (3,
             'John',
             'Rivers')

Step 2:
Create Stored Procedure that will return all results. In real time scenario you might have multiple SQL statements in SP such as create temp table, common table expressions, updates, deletes , and finally Select statement that is going to return us the results. All depends upon requirement for which the Stored Procedure is written for.

CREATE PROCEDURE dbo.Usp_customer

AS
  BEGIN
      SELECT ID,
             FName,
             LName
      FROM   dbo.Customer
  END 

Step 3:
Let's create view and execute our dbo.usp_Customer stored procedure by using OpenQuery.

CREATE VIEW dbo.vw_Customer

AS
  SELECT *
  FROM   OPENQUERY([SERVERNAME\MSSQL2008], 'EXEC Testdb.dbo.usp_Customer')

When we try to execute create view statement , we will get below error if Data Access is not enabled on SQL server.

Msg 7411, Level 16, State 1, Procedure vw_Customer, Line 3
Server 'SERVERNAME\MSSQL2008' is not configured for DATA ACCESS.

Let's enable DATA ACCESS on SQL Server Instance

EXEC sp_serveroption 'SERVERNAME\MSSQL2008', 'DATA ACCESS', TRUE

Once DATA ACCESS is enabled , Run the Create View statement. It should run fine.

Use created view in Query to confirm if all working fine.