[dba-SQLServer]Sproc Output to VB?

Mark Rider theoden11 at attbi.com
Sat May 3 21:57:06 CDT 2003


Thanks for the advice Doris.  I have changed the sproc and it runs in QA -
although there is no value returned to the grid view.

I still cannot seem to figure out the correct name to read in the recordset.  If
I look for rs_Fast("@MA") I get a "Item cannot be found in the collection" error
and any other name I try comes up with the same.

And I thought datetime storage and retrieval was a bear!

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Mike and Doris
Manning
Sent: Saturday, May 03, 2003 9:03 PM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer]Sproc Output to VB?


You haven't set @MA to anything so it is returning nothing. Try the following...

CREATE PROCEDURE [sp_LongMA] 
@inparam datetime,
@inparam2 datetime,
@MA money OUTPUT
AS
Select @MA = Avg (QQQValue) From tblQQQ  
Where  (QQQTime BETWEEN @inparam AND @inparam2)
GO

Doris Manning
Database Administrator
Hargrove Inc.
www.hargroveinc.com


-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Mark Rider
Sent: Saturday, May 03, 2003 9:44 PM
To: dba-sqlserver at databaseadvisors.com
Subject: [dba-SQLServer]Sproc Output to VB?


I have gone stupid looking for the answer to this.  I have a sproc that
calculates an average of several numbers, and runs fine in QA.  I cannot figure
out how to capture the output of the sproc so I can display it in anything other
than QA, MSQuery, etc.

The sproc is:
CREATE PROCEDURE [sp_LongMA] 
@inparam datetime,
@inparam2 datetime,
@MA money OUTPUT
AS
Select Avg (QQQValue) AS MovingAverage From tblQQQ  
Where  (QQQTime BETWEEN @inparam AND @inparam2)
GO

I call it from VB6 like so:

ssql_Fast = 
DECLARE @MA1 datetime 
DECLARE @MA2 datetime 
DECLARE @MA money 

SET @MA1 = '" & FastTime1 & " '
SET @MA2='" & FastTime2 & "' 
execute sp_LongMA @MA1, @MA2, @MA = @MA OUTPUT 

Again, it runs with no errors, but also with no indication of a returned value.

How do I write the @MA OUTPUT (the average) to a variable that I can access thru
VB6?   I set up an ADODB.Recordset in VB, call the sproc as above and get no
errors, but I cannot figure out what to look for in the returned recordset to
get the value of @MA.

TIA,

Mark Rider

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com



More information about the dba-SQLServer mailing list