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

Question

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.

Answer 1

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.