I need to return a value to VB.
I've tried returning a numeric value NUMERIC(25,20) via an output parameter but this didn't work. I'm know at a point in wich I created a bigint and multiplied the value so that the decimals are gone. However it only returns NULL?!?!?!?!!?!?
Here's part of my stored proc
CREATE PROCEDURE dbo.uspCalcWeightedAverage
@.StartDate2 varchar(10),
@.EndDate2 varchar(10),
@.InMarket nvarchar(50),
@.InProductType int,
@.InWeekDay int,
@.WeightedAverage bigint OUTPUT
AS
...
...
SELECT @.WeightedAverage = cast(10000000000 * (SUM(HHF.FACTOR) / COUNT(PDF.FLAG)) as bigint)
FROM
TBL_PRODUCTDEFS PDF
INNER JOIN #DATESBETWEENINTERVAL DBI ON DATEPART(HH, [DBI].[DATE]) = [PDF].[HOUR]
INNER JOIN tbl_historichourlyfactors HHF ON DATEPART(D,DBI.DATE) = HHF.DayID
AND [PDF].[HOUR] = [HHF].[HOUR]
AND DATEPART(M,DBI.DATE) = [HHF].[Month]
WHERE
PDF.MARKETID = @.InMarketID
AND PDF.PRODUCTTYPEID = @.InProductTypeID
AND [PDF].[WD-WE] = @.InWeekDay
AND HHF.MARKETID = @.InMarketID
AND PDF.FLAG = 1
GROUP BY FLAG
When I retrieve the output param it returns a NULL value. the properties in VB say that the parameter has the following props:
attribute 64 (Long)
NumericScale 0 (Byte)
Precision 19 (Byte)
Size 0 (ADO_LNGPTR)
Type adBigInt
Value Null
I try to return it with the following code (got the code from a friend)
Public Function RunProcedure(ByVal v_strStoredProcName As String, ByRef r_varParamValues() As Variant) As ADODB.Recordset
Dim objAdoRecordset As ADODB.Recordset
Dim objAdoCommand As ADODB.Command
Dim lngCtr As Long
On Error GoTo RunCommand_Error
' Create cmd object
Set objAdoCommand = New ADODB.Command
Set objAdoCommand.ActiveConnection = m_oAdoConnection
objAdoCommand.ActiveConnection = m_oAdoConnection
objAdoCommand.CommandText = v_strStoredProcName
objAdoCommand.CommandType = adCmdStoredProc
Call objAdoCommand.Parameters.Refresh
'Stop
For lngCtr = 0 To UBound(r_varParamValues)
If objAdoCommand.Parameters(lngCtr + 1).Direction = adParamInput Then
objAdoCommand.Parameters(lngCtr + 1).Value = r_varParamValues(lngCtr)
End If
Next
Set objAdoRecordset = New ADODB.Recordset
objAdoRecordset.CursorLocation = adUseClient
Set objAdoRecordset = objAdoCommand.Execute
'Stop
For lngCtr = 0 To objAdoCommand.Parameters.Count - 1
If objAdoCommand.Parameters(lngCtr).Direction = adParamOutput Or objAdoCommand.Parameters(lngCtr).Direction = adParamInputOutput Then
r_varParamValues(lngCtr - 1) = objAdoCommand.Parameters(lngCtr).Value
End If
Next
Set RunProcedure = objAdoRecordset
RunCommand_Exit:
' Collect your garbage here
Exit Function
RunCommand_Error:
' Collect your garbage here
Call g_oGenErr.Throw("WeatherFcst.CDbsConn", "RunCommand")
End Function
PLEASE HELP.
Regards,
Sander
Grrrr...If I add the following line (before the DROP) to the sp it returns the value!
SET @.WeightedAverage = 9587558855
?! Que Pasa ?!
