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

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

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
      SELECT ID,
      FROM   dbo.Customer

Step 3:
Let's create view and execute our dbo.usp_Customer stored procedure by using OpenQuery.
CREATE VIEW dbo.vw_Customer
  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

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.

1 comment:

  1. how this would work in the same server? as it should be always linked server correct?