August 21, 2001 at 2:06 am
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
August 21, 2001 at 4:46 am
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
August 21, 2001 at 3:52 pm
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.
Andy
August 21, 2001 at 10:57 pm
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
rs.MoveNext
Wend
' Need to close recordset before getting return
' and output parameters.
rs.Close
Debug.Print "Program ended with return code: " & Cmd(0)
Debug.Print "Total rows satisfying condition: " & Cmd(1)
cn.Close
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply