Last week, I wrote about how to share tables with stored procedures. This allows data sets to be passed in and then returned with modifications. In case you missed it, do read my post here: https://nakulvachhrajani.com/2025/08/04/0432-sql-server-tips-returning-parameters-from-stored-procedures-part-1-of-2/.

Returning result sets and sharing tables is great when there is little post processing involved. Applications that need to process individual entities, do so after the procedure returns them. Returning a cursor as an output parameter can be more efficient compared to returning a large result set. A forward-only cursor allows the application to iterate through the cursors and retrieve data as required v/s keeping the entire result set in memory.

An Example

Here’s an example on how to return cursors from a stored procedure. What I have here are two (2) stored procedures.

Procedure: The “inner” procedure – returning the cursor

USE [WideWorldImporters];
GO

IF OBJECT_ID('dbo.usrproc_GetSupplierList', 'P') IS NOT NULL
    DROP PROCEDURE dbo.usrproc_GetSupplierList;
GO

CREATE PROCEDURE dbo.usrproc_GetSupplierList
	@supplierListCursor CURSOR VARYING OUTPUT
AS
BEGIN
	SET NOCOUNT ON;

	SET @supplierListCursor = CURSOR FORWARD_ONLY STATIC
		FOR SELECT [ws].[SupplierID],
				   [ws].[SupplierName],
				   [ws].[SupplierReference]
			FROM [Website].[Suppliers] AS [ws] WITH (NOLOCK);
	OPEN @supplierListCursor;
END
GO

Consumer: The “outer” script – consuming the cursor

USE [WideWorldImporters];
GO

DECLARE @sqlTwinsCursor     AS CURSOR;
DECLARE @supplierIdentifier AS INT;
DECLARE @supplierName       AS NVARCHAR(100);
DECLARE @supplierReference  AS NVARCHAR(20);

--OUTPUT clause brings the CURSOR 
EXECUTE dbo.usrproc_GetSupplierList
    @supplierListCursor = @sqlTwinsCursor OUTPUT;

WHILE (@@FETCH_STATUS = 0)
    BEGIN
        FETCH NEXT FROM @sqlTwinsCursor INTO @supplierIdentifier, 
											 @supplierName, 
											 @supplierReference;

		--Consume the values 
		SELECT [ppo].[PurchaseOrderID],
		       [ppo].[SupplierID],
			   [ppo].[OrderDate],
			   [ppo].[ExpectedDeliveryDate],
			   [ppo].[DeliveryMethodID],
			   [ppo].[IsOrderFinalized]
		FROM [Purchasing].[PurchaseOrders] AS [ppo]
		WHERE [ppo].[SupplierID] = @supplierIdentifier
		  AND [ppo].[SupplierReference] = ISNULL (@supplierReference,[ppo].[SupplierReference]);
    END

--Close & deallocate the cursor
CLOSE @sqlTwinsCursor;

DEALLOCATE @sqlTwinsCursor;
GO

In case you are following along, here’s how the output should look like:

Cursor States

Cursor states only matter at return time. As outlined in the official documentation:

“It’s valid to close a cursor part of the way through the procedure, to open it again later in the procedure, and return that cursor’s result set to the calling batch, procedure, or trigger.”

I trust you will find the ability to return cursors from stored procedures helpful in improving the efficiency of your applications. Drop a note in the comments below on how you used it and the benefits you got.

Further Reading

• Returning Data from a stored procedure: https://learn.microsoft.com/en-us/sql/relational-databases/stored-procedures/return-data-from-a-stored-procedure?view=sql-server-ver17

Until we meet next time,

Be courteous. Drive responsibly.