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.