Administrator
  •  Admin
  • Advanced Member Topic Starter
2011-05-18T22:44:00Z
It is possible to use a stored procedure in a View ?

To use a stored procedure inside a View, you must create a linked server and use OPENQUERY statement:

Example:

Stored Procedure:
CREATE PROCEDURE [dbo].[sp_getContacts] 
AS
BEGIN
	SELECT * from CONTACTS
END
GO


Linked server:
Create a linked server to your SQL Server instance
No instance example:
Linked Server name: .

Named instance example:
Linked Server name: .\SQL1

Where SQL1 is instance name

View:
CREATE VIEW [dbo].[vStoredProcedureView]
AS
SELECT XXX FROM OPENQUERY([.\SQL1], '[myDatabaseName].[dbo].[sp_getContacts]') AS mySpInView

GO