Problem using COMPUTE with SPROC on ADO connection

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This will be obvious to some - but not me I'm afraid...

I am using an SQL data link from my ASP application to a SPROC - this all
works fine on standard SELECT statements and JOIN in to a datagrid in my
application. Rather than messing around with the code/server resource, I
thought I would add a COMPUTE line to the bottom of the SPROC to return a
total for one of the columns.

This all works fine in Query Analyser - I have a handle on using output
variables etc. but what is the best way to do this. I do not seem to be able
to get the COMPUTE statement to output to a variable - I get various erros
depending on what I try... Due to other reasons I want to avoid using GROUP
BY and COMPUTE BY...

This works in Query Analyser and outputs the COMPUTE statement - how do I
alter this to get the value in to a variable ? The input variables defined
below work fine and this query obviously executes following 2 input
parameters defined by the user viewing the site...

CREATE PROCEDURE SPROC

(
@Date datetime,
@SalesID char
)

AS

SELECT ****Various fields****

FROM ****3 different Databases with pre-defined JOIN****

WHERE (SOR_ORDER_HISTORY.ORDER_DATE = @Date AND
SOR_HISTORY_TRANS.TRAN_SPARE_NUMBER_2 = @SalesID)

ORDER BY SOR_ORDER_HISTORY.THIS_RECORD
COMPUTE SUM(SOR_HISTORY_TRANS.LINE_TOTAL)
GO

Thanks very much for your time
 
This question is better asked in a traditional ADO group or the SQL Server
groups. Regardless, here is some advice.

When you contact a sproc in Query Analyzer, any items, like your COMPUTE()
are thrown out to standard output. When you are working with an application,
you have to place the information somewhere.

To ADO: It is possible you are getting the COMPUTE output. If so, it is
placed in a seaparate Recordset, and you will have to get next to retrieve it.

Another option is to create an output parameter and set it to the COMPUTEd
value. You can then get the value of the parameter without having to move to
the second recordset in your "Recordset" object. NOTE that the earlier
versions of ADO do not have multiple Recordsets.


---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************
 
Back
Top