@@RowCount, adParamReturnValue 문제
해결책
http://www.experts-exchange.com/Programming/Languages/Scripting/ASP/Q_20269731.html
-----
First of all, I am assuming that the stored procedure you posted was merely for testing how to return a recordset AS WELL AS the RETURN_VALUE. Otherwise, there are a lot better ways of skinning this cat.
Having said that then here is an example that does work against the Northwind database on a SQL 7 database (I can also test it against a SQL 2000 database, if you wish). Caveat: I am using Visual Basic, but the end result should be the same (change Debug.Print for Response.Write)
CREATE Procedure sp_TestOutput
As
Select * From Customers
Return @@RowCount
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = "connection goes here"
.CommandType = adCmdStoredProc
.CommandText = "sp_TestOutput"
.Parameters(0).Value = -1
Set rs = .Execute
'Debug.Print .Parameters(0).Value
With rs
Do While Not .EOF
Debug.Print .Fields(0)
.MoveNext
Loop
.Close
End With
Set rs = Nothing
Debug.Print cmd(0)
End With
Set cmd = Nothing
Notes:
1. In order to read the RETURN_VALUE you must close the recordset before reading that value.
2. As an aside the name RETURN_VALUE has changed for consistency in SQL 2000 to @RETURN_VALUE.
3. Important: if you check the value of the return value, prior to closing out the recordset, it will stay empty. You an verify this by uncommenting the line immediately after the line:
Set rs = .Execute
4. cmd(0) is the same as:
cmd.Parameters(0).Value
cmd.Parameters("RETURN_VAL
cmd.Parameters("@RETURN_VA
It would seem that to have to close the recordset before getting the value of the RETURN_VALUE defeats the object. If this is the case, you could re-write the stored procedure as follows:
CREATE Procedure sp_TestOutput
As
Select * From Customers Where CustomerID ='CHOPS'
Select @@RowCount
And access it with the NextRecordset method. However, it does not buy a lot as you can see from the following code:
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = "connection goes here"
.CommandType = adCmdStoredProc
.CommandText = "sp_TestOutput"
.Parameters(0).Value = -1
Set rs = .Execute
With rs
Do While Not .EOF
Debug.Print .Fields(0)
.MoveNext
Loop
End With
Set rs = rs.NextRecordset
Debug.Print rs.Fields(0)
rs.Close
Set rs = Nothing
End With
Set cmd = Nothing
Anthony
두번째 해결책 .CursorLocation = adUseClient 로 해서 Recordset을 Open 한다.
(디폴트는 adUseServer, adOpenForwardOnly 이며 이것을 adUseClient, adOpenStatic 으로 변경하면
rs.RecordCount 도 바로 읽을 수 있고, @@rowcount 도 반영되어 있다.
이 페이지도 참조
http://p2p.wrox.com/archives/asp_databases/2001-10/74.php
asp_databases thread: recordset paging with stored procedure and .getrows
Im trying to set up an application that uses a call to a stored procedure to retrieves a recordset and outputs @@ROWCOUNT. based on the value of the @@ROWCOUNT output it then decides wether the results should be paged or not. The idea is based on Kens example of paging with a stored procedure and .getrows. The problem I'm running into is that I need the value of @@ROWCOUNT to decide what to do with the recordset so I'm forced int a situation where I have to run a objCommand.Execute to get @@ROWCOUNT and then objRS objCommand.Execute to get the recordset. Is there another way that I could go about this? it seems pretty silly (not to mention wastefull resources wise) to have to execute the the SP twice. Thanks in advance Jason
You can do all this with one stored procedure and return @@ROWCOUNT.
For example:
Create PROCEDURE test
AS
select * from table
RETURN @@ROWCOUNT
then do the following:
set objComm = Server.CreateObject ("ADODB.Command") 'CREATE DB OBJ, RUN SP, GET RESULTS
With objComm
.ActiveConnection = db
.CommandText = "test"
set rs = Server.CreateObject("ADODB.Recordset")
set p = .Parameters
p.Append .CreateParameter("@num_rows",adInteger,adParamReturnValue)
Set rs = .Execute
End With
rs.Close
intNoOfRecords = p(0).Value
rs.Open
intNoOfRecords is your rowcount (passed back in the first parameter). rs is
your recordset. Be sure to close rs to get the return value, or it won't
work. When you open it back up again, you still have access to the
recordset. Good Luck.
Tom Garrison
|