SQL Server Stored Procedure returning CURSOR as Output Parameter

SQL Server Stored Procedure returning CURSOR as Output Parameter

This tutorial explains How to return CURSOR as Output Paramater in SQL Server Stored Procedures. Which may not useful  for .NET Applications, It might be useful for T-SQL batch programs.

Create a Stored Procedure with Cursor as OUTPUT Param.

use profiledb

if object_ID('dbo.Get_All_Sports_In_Cursor','P') is not null
drop procedure dbo.Get_All_Sports_In_Cursor

go

create procedure Get_All_Sports_In_Cursor @p_sports_cursor cursor varying output
as

Set @p_sports_cursor = CURSOR
FORWARD_ONLY STATIC for
select * from sports;

open @p_sports_cursor;
return;

Consuming/Executing Stored Procedure which returns Cursor as Output Parameter.

USE [ProfileDB]
GO

declare @id int,@name varchar(20),@country varchar(2);
DECLARE    @return_value Int,
        @p_sports_cursor cursor

EXEC    @return_value = [dbo].[Get_All_Sports_In_Cursor]
        @p_sports_cursor = @p_sports_cursor OUTPUT

SELECT    'Return Value' = @return_value


FETCH NEXT FROM @p_sports_cursor into @id,@name,@country

while @@fetch_status =0
begin

print @concat(id,' ',@name,' ',@country)

FETCH NEXT FROM @p_sports_cursor into @id,@name,@country
end

Create a  Table called Sports

CREATE TABLE [dbo].[Sports] (
    [Id]      INT          IDENTITY (1, 1) NOT NULL primary key,
    [Name]    VARCHAR (20) NOT NULL,
    [Country] NCHAR (2)    NOT NULL,
   
);

Insert Data Into Sports table

insert into sports(name,country) values
('BaseBall','US'),('Hand FoodBall','US')

insert into sports(name,country) values
('Cricket','IN'),('Tennis','IN'),('Hockey','IN'),('Football','IN')

insert into sports(name,country) values
('Cricket','UK')

ID	Name	Country
1	Cricket	IN
2	Tennis	IN
3	Hockey	IN
4	Football	IN
5	BaseBall	US
6	Hand FoodBall	US
7	Cricket	UK

After Excuting  Stored Procedure OUTPUT would be

OUTPUT:
1 Cricket IN
2 Tennis IN
3 Hockey IN
4 Football IN
5 BaseBall US
6 Hand FoodBall US
7 Cricket UK


Tags: SQL Server Stored Procedure returning CURSOR as Output Parameter, Stored Procedure returning Cursor as Output Parameter, How to return CURSOR as Output Parameter, Creating SQL Server Stored Procedure, Executing SQL Server Stored Procedure ,SQL Server 2012 stored Procedures, Dropping Stored Procedures.