Reading cursor in C# from SQL Server's CURSOR parameter of stored procedure

user_6655022 0
Viewed 1590

I have a stored procedure in Microsoft SQL Server that looks similar to this:

ALTER PROCEDURE [MySchema].[TestTable_MGR_RetrieveLaterThanDate]
    @TestDate DATETIME, 
    @TableData CURSOR VARYING OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    SET @TableData = CURSOR FOR 
                         SELECT *
                         FROM MySchema.TestTable
                         WHERE @TestDate <= test_date;

    OPEN @TableData;
END

I need to call this from C#, but I have problems creating the SqlParameter object that is needed to hold the data of the output cursor.

The parameters I am creating look like this:

SqlParameter testDateParameter  = new SqlParameter();
testDateParameter.ParameterName = "@TestDate";
testDateParameter.Direction     = ParameterDirection.Input;
testDateParameter.SqlDbType     = SqlDbType.DateTime;
testDateParameter.Value         = theValue;

// I have no idea on what the correct SqlDbType should be here
SqlParameter tableDataParameter  = new SqlParameter();
tableDataParameter.ParameterName = "@TableData";
tableDataParameter.Direction     = ParameterDirection.Output;
tableDataParameter.SqlDbType     = SqlDbType.???;

I have tried (for the cursor parameter) both SqlDbType.Udt and SqlDbType.Structured but in both cases, I couldn't get what I wanted when calling the ExecuteReader method of the SqlCommand (exceptions in both cases). I tried those two because I did not see any option for cursors.

I understand cursors are usually not encouraged, but does .NET not allow at all reading of cursors from SQL Server stored procedures, or is there something I am missing?

Thank you in advance for all the help.

cursor stored-procedures sql-server c#
2 Answers

Below is the relevant excerpt from the Return Data from a Stored Procedure documentation:

The cursor data type cannot be bound to application variables through the database APIs such as OLE DB, ODBC, ADO, and DB-Library. Because OUTPUT parameters must be bound before an application can execute a procedure, procedures with cursor OUTPUT parameters cannot be called from the database APIs. These procedures can be called from Transact-SQL batches, procedures, or triggers only when the cursor OUTPUT variable is assigned to a Transact-SQL local cursor variable.

Although the doc doesn't call out SqlClient specifically, the consideration applies to all SQL Server APIs. I believe the restriction is because the underlying SQL Server TDS protocol does not support it. ADO.NET providers for some other DBMS products (e.g. Oracle) do support cursor parameters.

Other SQL Server client APIs do have the notion of cursors but those are implemented via system API stored procedures rather than T-SQL, using server-side statement handles and client API methods to use them. SqlClient, OTOH, is designed to stream data back to the client instead of maintaining server-side cursor state.

Although I do not recommend this technique, you could avoid the cursor output parameter by declaring a T-SQL global cursor in one stored proc and calling another on the same connection for RBAR.

CREATE OR ALTER PROCEDURE dbo.TestTable_MGR_RetrieveLaterThanDate
    @TestDate DATETIME
AS
SET NOCOUNT ON;
DECLARE TestTable_MGR_RetrieveLaterThanDate CURSOR GLOBAL FAST_FORWARD READ_ONLY
    FOR SELECT * -- consider an explicit column list here
        FROM MySchema.TestTable
        WHERE @TestDate <= test_date;
OPEN TestTable_MGR_RetrieveLaterThanDate;
GO

CREATE OR ALTER PROCEDURE dbo.FetchNext_TestTable_MGR_RetrieveLaterThanDate
AS
SET NOCOUNT ON;
FETCH NEXT FROM TestTable_MGR_RetrieveLaterThanDate;
IF @@FETCH_STATUS <> 0
BEGIN
    CLOSE TestTable_MGR_RetrieveLaterThanDate;
    DEALLOCATE TestTable_MGR_RetrieveLaterThanDate;
    RETURN @@FETCH_STATUS;
END;
GO
user_37111620

Ultimately, CURSOR doesn't like to be used like this in SQL Server, and while there are ways to use CURSOR in some scenarios, frankly it is almost never a good idea.

Since you are trying to use ExecuteReader with this, the logical conclusion is: just use SELECT:

ALTER PROCEDURE [MySchema].[TestTable_MGR_RetrieveLaterThanDate]
    @TestDate DATETIME
AS
BEGIN
    SET NOCOUNT ON;

    SELECT *
    FROM MySchema.TestTable
    WHERE @TestDate <= test_date;
END

This will work just fine with ExecuteReader.

Marc_Gravell_233540
Related