Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 672
  • Last Modified:

Return values from sp

I have an SQL Server stored procedure that execute SELECT with COMPUTE SQL statement. How can I retrieve data returned in compute part. I'm using Delphi 5 and TAdoStoredPRocedure to execute Sp.
SQL Statement looks like:
SELECT  id,duration,cost,revenue from table a
COMPUTE SUM(duration),sum(cost),sum(revenue)
0
pvs001d
Asked:
pvs001d
1 Solution
 
l8knightCommented:
You don't say which SQL server you are using but with interbase stored procedures are like this. I believe the RETURNS section is what your after.

extract from Interbase Docs...

CREATE PROCEDURE procedure_name
[( input_var1 datatype[ , input_var2 datatype …])]
[RETURNS ( output_var1 datatype[, output_var2 datatype …])]
AS
BEGIN
statements in InterBase procedure and trigger language
END

hope it helps

regards

l8knight
0
 
RadlerCommented:
Listening...
0
 
pvs001dAuthor Commented:
I'm using MS SQL Server . You probably misunderstoood my question. I need DELPHI syntax to get values that are returned by sp in multiple recordsets.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ECollinCommented:
You can use a TQuery to retreive the result of your sp.

It could be something like that :
myQry.SQL.Text := 'Select * from sp_YOUR_SP';
myQry.Open;
myQry.First;
While not myQry.EoF do
  Begin
  ...
  myQry.Next;
  End;
....
0
 
pvs001dAuthor Commented:
Guys,
please read the question before posting comments! I don't want to construct a query, I already have a stored procedure that (I think ) returns MULTIPLE recordsets . My problem is - how to get to the second, third ,etc recordsets using ADO.
0
 
DValeryCommented:
What SQL Server do you use?
0
 
XANCommented:
 Recordset2 := Recordset1.NextRecordset;
....
0
 
pvs001dAuthor Commented:
XAN , please lock the question. (Your answer might have been more detailed, though)
0
 
simonetCommented:
You can accept his answer w/o him lock the question. Just click on "accept comment as answer" on the header of his comment.
0
 
magicoCommented:
CREATE PROCEDURE spGetValueAndRecords
@CalcValue OUTPUT
AS

SELECT * FROM table
SELECT @CalcValue = SUM(value) FROM table


In Delphi
you add a TAdoStoredPRocedure, you open it, check the params and you'll see @CalcValue as Input/Output, change it to OutPut if you'll not use it to input values.

After Open you'll access @CalcValue as a regular param but for getting the paramvalue, not setting

Regards,
0
 
pvs001dAuthor Commented:
Comment accepted as answer
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now