Returning cursor from stored procedure

[Michael]

Hi all..

I have a stored procedure that needs to return a record from one of two
tables. I'm using an outpur parameter of type cursor. In the proc, I test if
the record exists in table 1, if so, retrieve and store in the output.
Otherwise retrieve the record from Table 2 and store in the output.

The problem is that there is no parameter type in ADO.Net to hold the
returned cursor. I tried using SqlCommandBuilder.DeriveParameters, and it
says the output param is of type int, which confuses me.

It's probably something simple, I hope..

Here's the stored proc..

CREATE PROCEDURE RetrieveRecord (@rec_id varchar(50), @ret_cur CURSOR
VARYING OUTPUT)
AS
DECLARE @exists bit
SELECT @exists = CASE WHEN COUNT(rec_pk) > 0 THEN 1 ELSE 0 END
FROM Table1 WHERE rec_pk = @rec_id

IF @exists = 1
BEGIN
DECLARE cur1 CURSOR LOCAL READ_ONLY FOR
SELECT rec_pk, description, status, submit_date
FROM Table1 WHERE rec_pk = @rec_id
OPEN cur1
SET @ret_cur = cur1
END
ELSE
DECLARE cur1 CURSOR LOCAL READ_ONLY FOR
SELECT rec_pk, description, status, submit_date
FROM Table2 WHERE rec_pk = @rec_id
OPEN cur1
SET @ret_cur = cur1

GO

 

[Patrice]

A cursor is a server side thing. You can't "return" this to a client.

Why don't you just issue a SELECT statement that will return a result set as
usual ? What is the goal of using a cursor ? You could also use output
parameters (one for each column) if this is what you are after...

Patrice

 

[Michael]

Thanks Patrice..

My idea was that since I didn't know which table the record would reside in,
I could use the if condition to choose which table to execute the select
against. The SQLServer docs led me to beleive that you could return a cursor
as an output parameter so it seemed to be a neat solution.

 

[David Browne]

Thanks Patrice..

My idea was that since I didn't know which table the record would reside
in,
I could use the if condition to choose which table to execute the select
against. The SQLServer docs led me to beleive that you could return a
cursor
as an output parameter so it seemed to be a neat solution.

You can just have SELECT statement in an IF statement to return conditional
result sets.

Just run this procedure with ExecuteReader or DataAdapter.Fill()

David

CREATE PROCEDURE RetrieveRecord (@rec_id varchar(50))
AS
DECLARE @exists bit
SELECT @exists = CASE WHEN COUNT(rec_pk) > 0 THEN 1 ELSE 0 END
FROM Table1 WHERE rec_pk = @rec_id

IF @exists = 1
BEGIN
SELECT rec_pk, description, status, submit_date
FROM Table1 WHERE rec_pk = @rec_id
END
ELSE
BEGIN
SELECT rec_pk, description, status, submit_date
FROM Table2 WHERE rec_pk = @rec_id
END
GO

 

[Patrice]

This is not needed. Just return a different result set based on your
condition :

IF @MyBoolean=1
SELECT a,b FROM MyTable
ELSE
SELECT b,c,d FROM SomeOtherTable

The documentation likely meant that you can pass a CURSOR parameter back and
forth *server side* accross stored procedures...

Patrice


--