Calling Stored procedure having cursor from ASP pa

  • Hello,

    I am having problem with calling a stored procedure which is having a cursor inside storing a large amount of data.

    I am able to retreive the a part of data by returning the value in a variable through output parameters. But the problem is that with time the data will increase and the value of varibale will become less for the data to be passed to ASP page.

    Can anyone suggest anyway to directly print the cursor in the asp script.

    If i am running the procedure in quesry analyzer the output is perfect.

    SOS for this

  • how about using a temporary table or a table variable .. insert the values in this table and at the end of the procedure just run a select on the table .. you can use the recordset returned by the procedure in ASP ..

    will this work for you

  • I'd recommend against using a server cursor for writing out to asp. A better solution is to pull the data into an ADO recordset (even if you have to go through an intermediate step or two to get it there), then you just movenext through the recordset and response.write out. Still, as the data grows, you'll probably want to look at some sort of paging mechanism, users arent going to wait for 100,000 row pages to finish drawing.


  • I agree with Andy, get rid of the cursor. You can populate a recordset with from your stored procedure. Just use the command:

    Set rs = command.execute

    Remember: if you do this and your stored procedure also returns output parameters in addition to the recordset you must close the recordset before you can access the output parameters.

    VB Sample:

    ' Set up a command object for the stored procedure.

    Set cmd.ActiveConnection = cn

    cmd.CommandText = "myProc"

    cmd.CommandType = adCmdStoredProc

    ' Set up a return parameter.

    Set param1 = cmd.CreateParameter("Return", adInteger, adParamReturnValue)

    cmd.Parameters.Append param1

    ' Set up an output parameter.

    Set param2 = cmd.CreateParameter("Output", adInteger, adParamOutput)

    cmd.Parameters.Append param2

    ' Set up an input parameter.

    Set param3 = cmd.CreateParameter("Input", adInteger, adParamInput)

    cmd.Parameters.Append param3

    royalty = Trim(InputBox("Enter royalty:"))

    param3.Value = royalty

    ' Execute command, and loop through recordset, printing out rows.

    Set rs = cmd.Execute

    Dim i As Integer

    While Not rs.EOF

    For Each fldloop In rs.Fields

    Debug.Print rs.Fields(i)

    i = i + 1

    Next fldloop

    Debug.Print ""

    i = 0



    ' Need to close recordset before getting return

    ' and output parameters.


    Debug.Print "Program ended with return code: " & Cmd(0)

    Debug.Print "Total rows satisfying condition: " & Cmd(1)


Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply