Returning Values from a Stored Procedure
Stored Procedure which resides on the Sybase Server.
create proc num_sales
(@tid char(6), @tot_sales int output)
as
select @tot_sales = sum(qty)
from salesdetail
where title_id = @tid
return
If I ran the query from ISQL it would be:
declare @total int exec num_sales "PS2091", @total output select @total
How would I code this and get the result from my VB application? I have tried many different things and read tons of documentation and no where could I find an example. Any help would be greatly appreciated.
From: Pete Roding
Email: RODINPJ@NU.COM
Your code calls the stored procedure which writes the result you want into a variable, you then grab that variable. The way to do this in VB is to combine all of those things into the stored procedure itself, so that you can do it all in one call. The other thing to be aware of is that ODBC allows stored procedures to return result sets. Sybase supports this by returning a result set from the last select statement in your stored procedure. For example, you could have a stored procedure which is simply a select statement and it would return the same recordset that directly executing that select statement would return. You are fortunate in this regard that Sybase supports this, some other servers do not - that's a real pain. MS SQL Server supports returning multiple recordsets, although ODBC will only allow you to return one.
Essentially you can rewrite (and simplify) your stored procedure to be like this:
create proc num_sales
(@tid char(6))
as
select sum(qty) totalsales
from salesdetail
where title_id = @tid
return
This way the stored procedure simply returns a recordset containing one record and one value. The expression sum(qty) is aliased as totalsales so that we return a column with a name we know (we could alternately say 'sum(qty) = totalsales' either will work). So the resultset returned from this stored procedure will have one row and one column called totalsales which contains the value you want.
You would call it from VB4 like this:
Dim rs as Recordset
Set rs = db.OpenRecordset("exec num_sales ""PS2091""")
msgbox rs!totalsales, 64, "Total Sales"
rs.Close
Remember that the result is a normal recordset as far as DAO is concerned so you can use the normal tricks, like addressing the returned value as rs.Fields(0) instead of using the name.
You can expand on this by using a stored procedure to calculate a number of values, writing them into variables and then returning them all by writing a select statement that returns all those variables aliased. But that's starting to get far too exciting. Enough of that.
I hope this explains what you needed to know to start returning things from your stored procedures, both in this case and in general.
I also have to make special mention at this time of some help I had with answering the Sybase specifics of this question: Mark Warhol, author of 'The Art of Visual Basic Programming' (an excellent read!), and VB/Sybase guru, was kind enough to help me out with those details.
