how to call stored procedure with cursor parameter from asp.net?

Question

0.00/5 (No votes)

See more:

i created one procedure in sql server with cursor varying output parameter
how to access this procedure from asp.net page and bind data to gridview

ex:
create proc sp_getdata @mycur cursor varying output
as
begin
set @mycur=cursor static for select * from emp
open @mycur
end

execution:
declare @mydatacur cursor
exec sp_jemp_getdata @mycur=@mydatacur output
fetch next from @mydatacur
while @@fetch_status=0
begin
fetch next from @mydatacur
end

Posted 13-Jul-10 0:12am

chjoseph

Updated 13-Jul-10 0:39am

v5

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Christian Graus · Answer 1 · 2010-07-13T00:31:00

Solution 1

That's not really how you're supposed to use cursors. I'm not sure this can even work. You should write a proc that uses the cursor to fill a table.

Posted 13-Jul-10 0:31am

Christian Graus

gmtzgtz · Answer 2 · 2011-10-19T05:27:00

Try the below code to call your SP. Then you can easily bind the result set to your gridview. Just do a gv.DataSource = DataSet and then gv.Bind()

SqlConnection lSQLConn = null;
SqlCommand lSQLCmd = new SqlCommand();
//Declare a DataAdapter and a DataSet
SqlDataAdapter lDA = new SqlDataAdapter();
DataSet lDS = new DataSet();
 
//...Execution section
 
// create and open a connection object
lSQLConn = new SqlConnection(connStr);
lSQLConn.Open();
//The CommandType must be StoredProcedure if we are using an ExecuteScalar
lSQLCmd.CommandType = CommandType.StoredProcedure;
lSQLCmd.CommandText = "sp_YourSPName"; 
lSQLCmd.Parameters.Add(new SqlParameter("@Parm1", aParm1));
lSQLCmd.Parameters.Add(new SqlParameter("@Parm2", aParm2));
lSQLCmd.Parameters.Add(new SqlParameter("@Parm3", aParm3));
lSQLCmd.Parameters.Add(new SqlParameter("@Parm4", aParm4));
 
lSQLCmd.Connection = lSQLConn;
//Fill the DataAdapter with a SelectCommand
lDA.SelectCommand = lSQLCmd;
lDA.Fill(lDS);

To see a full explanation go to:
Executing a Stored Procedure from a WCF Service Method C#