Handle return open cursor from store procedure?

  • Thread starter Thread starter Tommy
  • Start date Start date
T

Tommy

I am not quite familiar with how to handle a opened cursor from the store
procedure using SqlParameter class, how should I define the parameters. And
any special techniques?

My store procedure:
CREATE PROC usp_6_14_1
@vchTitleID VARCHAR(6) = ¡®%¡¯,
@curOrders CURSOR VARYING OUTPUT
AS
SET @curOrders = CURSOR FORWARD_ONLY STATIC FOR
SELECT title_id, stor_id, qty, ord_date
FROM sales
WHERE title_id LIKE @vchTitleID
ORDER BY ord_date
OPEN @curOrders
GO

Thanks in advance
 
Tommy said:
I am not quite familiar with how to handle a opened cursor from the store
procedure using SqlParameter class, how should I define the parameters. And
any special techniques?

My store procedure:
CREATE PROC usp_6_14_1
@vchTitleID VARCHAR(6) = ¡®%¡¯,
@curOrders CURSOR VARYING OUTPUT
AS
SET @curOrders = CURSOR FORWARD_ONLY STATIC FOR
SELECT title_id, stor_id, qty, ord_date
FROM sales
WHERE title_id LIKE @vchTitleID
ORDER BY ord_date
OPEN @curOrders
GO
Click to expand...

afaik, you can't use SqlServer cursors in client code. In Oracle you can
bind a REF CURSOR to a datatable which will fill the datatable, but with
Sqlserver there is no such logic, if I'm not mistaken. So there is no way
you'll be able to grab the cursor from sqlserver and actually be able to do
something with it. Cursor parameters are only useful among stored procedures:
one proc calls another one to create a cursor and then uses that cursor in
T-SQL to populate variables for example.

Frans.
 
Hi,

Just an addition. Since your SP does not do anything special, but just a
selection of the data, you could use DataReader in ADO.NET to open
read-only, forward-only cursor. You will get exact same result, which you
have right now, but with the limitation, that you cannot change any values
 
Thanks to you all.
-----Original Message-----
Hi,

Just an addition. Since your SP does not do anything special, but just a
selection of the data, you could use DataReader in ADO.NET to open
read-only, forward-only cursor. You will get exact same result, which you
have right now, but with the limitation, that you cannot change any values

--
Val Mazur
Microsoft MVP





.
Click to expand...
 
Ah no. You can use server-side cursors with ADO.NET and I show how it's done
in my article. See http://www.betav.com/pinnacle.htm. I don't use SPs to
create them, but that's entirely possible.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Back
Top