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',
                                   @srvproduct=N'',
                                   @provider=N'SQLNCLI',
                                   @datasrc=N'YOURSERVERNAME\YOURINSTANCE'
      
SELECT  * FROM OPENQUERY(localhost,'exec sp_who')
--http://msdn.microsoft.com/en-us/library/ms190479.aspx

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

The cleanest example I have seen for this feature is by Sergey Akopov.

Sergey implement a jQuery example that works wonderfully! Here is the link to the article. I attached the ZIP file same code, which is on Sergey's site as well.

MaintainScroll.zip (3.01 kb)

Here is a link to a pure JavaScript example (older 2006). I have not tried it out but wanted to save the link as another example.