Returning cursor from stored procedure

  • Thread starter Thread starter Michael
  • Start date Start date
M

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
 
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
 
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.
 
Michael said:
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.
Click to expand...

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


--
 
Back
Top