Carolinat
asked on
How can I return a cursor from a stored procedure to a data grid
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
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
>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...
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...
ASKER
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.?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Excellent!!!!
Thanks, I'm going to try.
Thanks, I'm going to try.
"
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
"