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
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.
Step 3:
Let's create view and execute our dbo.usp_Customer stored procedure by using OpenQuery.
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
Use created view in Query to confirm if all working fine.
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.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.