1

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Link Marston
  • 37
  • 1
  • 8
  • I would be amazed if this worked, frankly, but: what exception are you seeing? Note: you wouldn't use ExecuteReader here, as it doesn't actually issue a select - you'd just use ExecuteNonQuery, however ... what is it that you're actually trying to *do* here? What is the objective? What would be wrong with just a SELECT (and forget the CURSOR)? – Marc Gravell Sep 19 '20 at 07:49
  • With the SqlDbType.Udt, you need a UdtTypeName too, and of course "CURSOR" is not allowed (exception being I don't have privileges to use CURSOR as UDT type name). With the SqlDbType.Structured, I am getting a different error: one with size (it's expecting me to know, in advance of executing the sql command, how many rows will be contained in the cursor) and the other being that parameterdirection output is not supported for TableData. – Link Marston Sep 19 '20 at 07:52
  • In regards to replacing the CURSOR with the SELECT, that is not something our team likes to pursue: we believe the SQL code has to be in stored procedures only, so all select statements are only allowed within stored procedures. Unfortunately it's not something we can get around. Funny thing is that the Oracle DLLs (DataAccess and ManagedDataAccess) both allow RefCursors and they work just fine, but SQL Server doesn't for some reason. I find it incredibly odd. – Link Marston Sep 19 '20 at 07:57
  • 1
    The "select in a stored procedure" - sure, no problem; I mean, it is subjective, contested, and contextual, but if you like it: fine - you do you. But that doesn't explain why you want to use CURSOR for this. That is *very* unusual, and it isn't going to make life easy. So: why a *cursor* here? What is it that you're trying to achieve that you can't do by having your proc issue a `select` directly, with no cursor involvement? That's what virtually all APIs are designed to expect here. It is what ExecuteReader expects, for example, which is the API you seem to be trying to use – Marc Gravell Sep 19 '20 at 08:01
  • I'm trying to ask this constructively, note; I'm hugely interested in your requirements here (I'm the main author of Dapper, so I have a huge curiosity in such things) – Marc Gravell Sep 19 '20 at 08:04
  • I am not sure what I am being asked, honestly. The purpose of the stored procedure is to select rows from a table and to return them to C#, which will analyze them and potentially alter some of those down the line (through other stored procedures). The ExecuteReader was used because, although obviously different, in Oracle we use just that when we have an output refcursor and it works just fine. Granted, SQL Server is different from Oracle, but conceptually ExecuteReader should work in this case too. – Link Marston Sep 19 '20 at 08:19
  • I disagree; `ExecuteReader` should absolutely *not* work with the code as shown, as it does not yield a result grid (it has an output patameter that is a cursor that has results, but that is completely different). If you want to use `ExecuteReader` with this, your SP would *just* be `SELECT * FROM MySchema.TestTable WHERE @TestDate <= test_date;` with no mention of cursors or output parameters, and it would work fine. So if all you're trying to do is have a SP that works with `ExecuteReader` to return results: *that's how you would do that* – Marc Gravell Sep 19 '20 at 08:23
  • Ok. I'll give it a shot. Thank you. I still find it odd that the SQL Server cursor is not implemented in C#... – Link Marston Sep 19 '20 at 08:29
  • 1
    cursors are actively discouraged in just about every way, so: not a huge shocker to me, frankly; there aren't any advantages I can think of for a cursor over a regular select, and there are lots of disadvantages – Marc Gravell Sep 19 '20 at 08:39

2 Answers2

0

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
Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
0

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
  • 1,026,079
  • 266
  • 2,566
  • 2,900