I have a procedure that returns data that is used to populate a grid. I wanted to use the same procedure to pull out x number of rows from another procedure. I thought if I could call a the stored procedure on a cursor vs. using a select statement I could reuse the procedure.
I came across an article that covered OPENQUERY. Basically you create a linked server on the same database you want to access. Then in this way you can call a SQL Server Stored Procedure in a select statement and use it as part of a cursor!
--1. Allow Data Access to this SQL Instance
exec sp_serveroption @server = 'YOURSERVERNAME\YOURINSTANCE'
,@optname = 'DATA ACCESS'
,@optvalue = 'TRUE'
--2. Use navtive OLEDB provider. server name set as localhost. Could be anything
EXEC master.dbo.sp_addlinkedserver @server = N'localhost',
SELECT * FROM OPENQUERY(localhost,'exec sp_who')
On one SQL Server 2008 the query executes fine, on another I had to include the following:
SELECT * FROM OPENQUERY(localhost,'SET FMTONLY OFF; SET NOCOUNT ON;exec MyDB.dbo.prc_get_some_data')
Reference this post on StackOverflow