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.
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.