Cursor as an output parameter

I have a stored procedure in SQL Server 2008 R2 that returns a Cursor as an OUTPUT value. Is it possible to return this Cursor to a C# application using the .NET 4.0 framework?

My Stored procedure has the following signature:

CREATE PROCEDURE [dbo].[MyProcedure]
      @nuserid    int,
      @nfetchtype int,
      @returncursor CURSOR VARYING OUTPUT
AS
...

At the end of the stored procedure I have

...
    set @returncursor = CURSOR FORWARD_ONLY STATIC for SELECT * FROM MyTable WHERE          column=@Value
    open @returncursor
END

In my C# Web Application, I use a wrapper that utilizes System.Data.DbType and I set my return value/parameter as DbType.Object. When I execute the query against the database, I get the following error:

Operand type clash: sql_variant is incompatible with cursor

This DbType.Object parameter will work for a REFCURSOR in Oracle but I'm curious to see if there is a way to do the same for SqlServer. My goal is to have stored procedures in both SqlServer and Oracle that have the exact same signature.

Answers


You can have a CURSOR parameter in SQL, but ADO.NET can't use it. Instead, in .NET, you have to use the IDataReader returned by IDbCommand.ExecuteReader(), and use NextResult() to iterate over your result sets.


You are not supposed to use cursors in database APIs such as OLE DB, ODBC, ADO, and DB-Library etc.

Please go through this article before using the cursors.


Need Your Help

Video streaming from Android device to LAMP Server

php android sockets video-streaming

starting from this point: http://www.mattakis.com/blog/kisg/20090708/broadcasting-video-with-android-without-writing-to-the-file-system

#1010 AS3 error 2D Array

actionscript-3 2d undefined isometric

Can't seem to find any solution to my problem right now.