Reference Cursor in Stored Procedure

Reference cursors are specific to oracle, it is mostly used to output an unstructured cursor dataset from a stored procedure or to exchange data between stored procedures, when it comes to SQL Server the stored procedures automatically outputs the data for more info click here, if you would like to exchange the data between stored procedures look below for example

 

If you still want the equivalent in SQL Server then it is “CURSOR VARYING OUTPUT” which I will explain in the bottom of this article

 

Oracle

 

For example lets create a temporary table

CREATE TABLE SAM_TEMP

(

COL1 NUMBER NULL,

COL2 VARCHAR2(100) NULL

)

 

Now let’s create a stored procedure which outputs reference cursor (SYS_REFCURSOR) C_RESULT which just pulls data SAM_TEMP and that’s it we have outputted a reference cursor from the stored procedure.

 

CREATE OR REPLACE

PROCEDURE SP_OUT_REFCUR_PARAM(C_RESULT OUT SYS_REFCURSOR )

IS

BEGIN

OPEN C_RESULT FOR

SELECT COL1, COL2 FROM SAM_TEMP ; --replace this statement with your script

END SP_OUT_REFCUR_PARAM;

 

Outputting a cursor from the stored procedure is the easy part, to retrieve a reference cursor into a table we need to know the structure of data inside the refcursor

 

So as we already know the data in this cursor is from SAM_TEMP table we can directly fetch it as shown below

 

DECLARE

REFCUR SYS_REFCURSOR; --cursor to collect data from stored procedure

OUTTABLE SAM_TEMP%ROWTYPE ; --creating record type variable of SAM_TEMP table record type

BEGIN --getting data from cursor and looping through the data

SP_OUT_REFCUR_PARAM(REFCUR);

LOOP

FETCH REFCUR INTO OUTTABLE;

EXIT WHEN REFCUR%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(OUTTABLE.COL1);

END LOOP;

CLOSE REFCUR;

END;

 

What if we have multiple tables which are joined in our query and we do not have a single table to get the structure of data, that’s when we need to create a record type of expected data structure and then create a variable on top of it and write the data into that variable

 

DECLARE

REFCUR SYS_REFCURSOR; -- cursor to collect data from stored procedure

TYPE RECORDTYPE IS RECORD (COL1 NUMBER, COL2 VARCHAR2(100)); -- create record type with needed columns

OUTTABLE RECORDTYPE; -- create actual record type variable

BEGIN -- getting data from cursor and looping through the data

SP_OUT_REFCUR_PARAM(REFCUR);

LOOP

FETCH REFCUR INTO OUTTABLE;

EXIT WHEN REFCUR%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(OUTTABLE.COL1);

END LOOP;

CLOSE REFCUR;

END;

 

Another and a better way is using bulk collect

 

DECLARE

REFCUR SYS_REFCURSOR; -- cursor to collect data from stored procedure

TYPE REFTABLETYPE IS RECORD (COL1 NUMBER, COL2 VARCHAR2(100)); -- create record type with needed columns

TYPE TABLETYPE IS TABLE OF REFTABLETYPE; -- create table of REFTABLETYPE type

OUTTABLE TABLETYPE; -- create actual table variable

BEGIN --getting data from cursor and looping through the data

SP_OUT_REFCUR_PARAM(REFCUR);

FETCH REFCUR BULK COLLECT INTO OUTTABLE;

FOR I IN OUTTABLE.FIRST..OUTTABLE.LAST LOOP

DBMS_OUTPUT.PUT_LINE(OUTTABLE(I).COL1);

END LOOP;

CLOSE REFCUR;

END;

 

SQL Server

 

If you would like to exchange the data between stored procedures we can either use session temporary tables (#tablename) which just exist for the session and can be seen just by the session

Or we can use a table valued parameter, lets discuss both the ways below

 

Exchange data using session temporary tables:

 

-- create the stored procedure which needs to be accessed inside another stored procedure

CREATE PROCEDURE [DBO].[SP_OUT_SAM_TEMP]

AS

BEGIN

-- replace this sub query with your script

SELECT * FROM (

SELECT CAST('OUTPUT SAM1' AS VARCHAR(100)) AS COL1,CAST( 1 AS INT) AS COL2

UNION ALL

SELECT CAST('OUTPUT SAM2' AS VARCHAR(100)) AS COL1,CAST( 2 AS INT) AS COL2

) SAM_TEMP

 

END

 

 --Now let’s access the above stored procedure inside a stored procedure

CREATE PROCEDURE DBO.SP_USE_SAM_TEMP

AS

BEGIN

            --if exists drop the temp table

IF OBJECT_ID('TEMPDB..#TEMP_TABLE') IS NOT NULL

BEGIN

DROP TABLE #TEMP_TABLE

END

 --create a temp table

CREATE TABLE #TEMP_TABLE (COL1 VARCHAR(100), COL2 INT)

--insert into the temp table 

INSERT INTO #TEMP_TABLE

EXEC [DBO].[SP_OUT_SAM_TEMP]

--access the temp table 

SELECT * FROM #TEMP_TABLE

END

Output:

EXEC DBO.SP_USE_SAM_TEMP

 

Exchange data using table variable:

 

-- create the stored procedure which needs to be accessed inside another stored procedure

CREATE PROCEDURE [DBO].[SP_OUT_SAM_TEMP]

AS

BEGIN

-- replace this sub query with your script

SELECT * FROM (

SELECT CAST('OUTPUT SAM1' AS VARCHAR(100)) AS COL1,CAST( 1 AS INT) AS COL2

UNION ALL

SELECT CAST('OUTPUT SAM2' AS VARCHAR(100)) AS COL1,CAST( 2 AS INT) AS COL2

) SAM_TEMP

 

END 

 

 --Now let’s access the above stored procedure inside a stored procedure

CREATE PROCEDURE DBO.SP_USE_SAM_TEMP

AS

BEGIN

            --declare table variable

DECLARE @TEMP_TABLE TABLE (

COL1 VARCHAR(100)

, COL2 INT

)

--insert into the table variable

INSERT INTO @TEMP_TABLE

EXEC [DBO].[SP_OUT_SAM_TEMP]

--access the temp variable 

SELECT * FROM @TEMP_TABLE

 

END

Output:

EXEC DBO.SP_USE_SAM_TEMP

 

 

Exchange data using table valued parameter:

If you would like to use table valued parameter then we need to create a table type for the table valued parameter and then define a variable on top of it.

 

Note: table valued parameter cannot be updated they are insert only.

 

 

-- Create a User Defined Table Type.

CREATE TYPE SAM_TEMP_TYPE

AS TABLE

(

COL1 VARCHAR(100)

, COL2 INT

);

GO

 

-- create the stored procedure which needs to be accessed inside another stored procedure

CREATE PROCEDURE [DBO].[SP_OUT_SAM_TEMP]

AS

BEGIN

-- replace this sub query with your script

SELECT * FROM (

SELECT CAST('OUTPUT SAM1' AS VARCHAR(100)) AS COL1,CAST( 1 AS INT) AS COL2

UNION ALL

SELECT CAST('OUTPUT SAM2' AS VARCHAR(100)) AS COL1,CAST( 2 AS INT) AS COL2

) SAM_TEMP

 

END 

 

 --Now let’s access the above stored procedure inside a stored procedure

CREATE PROCEDURE DBO.SP_USE_SAM_TEMP

AS

BEGIN

            --declare table valued parameter of SAM_TEMP_TYPE

DECLARE @TEMP_TABLE AS SAM_TEMP_TYPE;

--insert into the table valued parameter

INSERT INTO @TEMP_TABLE

EXEC [DBO].[SP_OUT_SAM_TEMP]

--access the table valued parameter

SELECT * FROM @TEMP_TABLE

 

END

Output:

EXEC DBO.SP_USE_SAM_TEMP

 

If you still think you need reference cursor in SQL Server and above ways doesn’t work then lets see how to implement the Reference cursor in SQL Server

 

Let’s create a stored procedure to output a reference cursor

 

CREATE PROCEDURE [DBO].[SP_OUT_SAM_TEMP]

@C_RESULT CURSOR VARYING OUTPUT

AS

BEGIN

SET NOCOUNT ON;

SET @C_RESULT = CURSOR

FORWARD_ONLY STATIC FOR

-- replace this sub query with your script

SELECT * FROM (

SELECT CAST('OUTPUT SAM1' AS VARCHAR(100)) AS COL1,CAST( 1 AS INT) AS COL2

UNION ALL

SELECT CAST('OUTPUT SAM2' AS VARCHAR(100)) AS COL1,CAST( 2 AS INT) AS COL2

) SAM_TEMP

OPEN @C_RESULT;

END;

 

Now let’s access the data in the reference cursor, as you can see below we cannot fetch the cursor directly into a table instead we need to fetch into columns and then insert into the table variable.

 

DECLARE @OUTTABLE TABLE(COL1 VARCHAR(100),COL2 INT)

DECLARE @COL1 VARCHAR(100),@COL2 INT

, @REFCUR CURSOR

 

EXEC [DBO].[SP_OUT_SAM_TEMP] @C_RESULT = @REFCUR OUTPUT

FETCH NEXT FROM @REFCUR INTO @COL1,@COL2

WHILE (@@FETCH_STATUS = 0 )

BEGIN

INSERT INTO @OUTTABLE

SELECT @COL1,@COL2

FETCH NEXT FROM @REFCUR INTO @COL1,@COL2

END

CLOSE @REFCUR

DEALLOCATE @REFCUR

SELECT * FROM @OUTTABLE

 

Thank you for reading this article, check out my other SQL Server vs. Oracle posts.