[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How can I return a cursor from a stored procedure to a data grid

Posted on 2006-05-24
5
Medium Priority
?
247 Views
Last Modified: 2011-09-20
Hi Guys,
I have a select that I need to convert into a cursor in order to pass it to the data grid.
1. This is the stored procedure

CREATE   PROCEDURE SP_1
@PAR1             NUMERIC(8,0),
@PAR2             NUMERIC(9,0)
AS
BEGIN
SELECT  P.NAME
FROM  PROVIDERS AS P,
           OTHERPROVIDERS AS DP
WHERE       DP.PROVIDERID      = P.PROVIDERID
  AND       DP.COD1            = @PAR1
  AND       DP.COD2                        = @PAR2
RETURN
END

2. I'm using c#, asp.net 2003 and I need to conect my form with this cursor. Where, how and what should I do to acomplish this funcionality?

Thank you very much.....
Carol  
0
Comment
Question by:Carolinat
  • 2
  • 2
5 Comments
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 16755610
I'm not familiar with this process, but here's the example from Books Online (under "CREATE PROCEDURE"):
"
E. Use an OUTPUT cursor parameter
OUTPUT cursor parameters are used to pass a cursor that is local to a stored procedure back to the calling batch, stored procedure, or trigger.

First, create the procedure that declares and then opens a cursor on the titles table:

USE pubs
IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'titles_cursor' and type = 'P')
DROP PROCEDURE titles_cursor
GO
CREATE PROCEDURE titles_cursor @titles_cursor CURSOR VARYING OUTPUT
AS
SET @titles_cursor = CURSOR
FORWARD_ONLY STATIC FOR
SELECT *
FROM titles

OPEN @titles_cursor
GO

Next, execute a batch that declares a local cursor variable, executes the procedure to assign the cursor to the local variable, and then fetches the rows from the cursor.

USE pubs
GO
DECLARE @MyCursor CURSOR
EXEC titles_cursor @titles_cursor = @MyCursor OUTPUT
WHILE (@@FETCH_STATUS = 0)
BEGIN
   FETCH NEXT FROM @MyCursor
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
GO
"
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16755674
>I have a select that I need to convert into a cursor in order to pass it to the data grid.
I wonder why you would need a cursor to be returned in SQL Server to be used in a data grid?

you would simply define a SQLCommand of type storedprocedure, with the name of the stored procedure as command text, append the 2 parameters, and use the command to fill the data set. with a sqladapter you can then bind to the grid...
0
 

Author Comment

by:Carolinat
ID: 16755749
The store procedure is going to return more than one row. Do I need a cursor? or Do I need to use a regular select.?
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 16755814
>The store procedure is going to return more than one row. Do I need a cursor?
no. only in oracle you have to use the ref cursor parameter "trick" to return recordsets

>or Do I need to use a regular select.?
simple select should do the job
0
 

Author Comment

by:Carolinat
ID: 16755894
Excellent!!!!
Thanks, I'm going to try.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question