[dba-SQLServer]Sproc Output to VB?

Thanks Jim, that was the missing link!

I finally have what I need to start working on the really big sproc!

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence
(AccessD)
Sent: Sunday, May 04, 2003 2:12 AM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer]Sproc Output to VB?


Hi Mark:

Have you got the right parameter code to receive the call back information?
Something like this:

objCmd.Parameters.Append .CreateParameter("@intCompanyCode", adInteger,
adParamInput, , lngCompanyCode) objCmd.Parameters.Append
.CreateParameter("@intRecords", adInteger,
adParamOutput)

After the SP is executed the information is returned in an array:

varRecordValue = objCmd(1)

HTH
Jim

-----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 7:57 PM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer]Sproc Output to VB?


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

_______________________________________________
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