[dba-SQLServer]Sproc Output to VB?

Mike and Doris Manning mikedorism at ntelos.net
Tue May 6 07:28:30 CDT 2003 

It all becomes a question of which side you want to handle the bulk of the
processing.
By using recordsets, all processing happens on the CLIENT side.
By using output parameters, all processing happens on the SERVER side.

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 L.
Breen
Sent: Tuesday, May 06, 2003 6:43 AM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer]Sproc Output to VB?


Hello Guys,

In relation to this, I have asked the list previously, but do you find that
using commands and using OutPut parameters are arkward?

I try to avoid using output params if possible, also, I have been recently
using simple recordsets to run action sp's with nice tidy results.

It means that the parameters are loosely typed, but I am happy enough with
it,

any comments ?


Mark



----- Original Message -----
From: "Mark Rider" <theoden11 at attbi.com>
To: <dba-sqlserver at databaseadvisors.com>
Sent: Sunday, May 04, 2003 3:35 PM
Subject: RE: [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
>
> _______________________________________________
> 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