Link to home
Start Free TrialLog in
Avatar of Edward Harford
Edward Harford

asked on

Testing for Null in Stored Proc Return Value

I have a Function which might return a Null Value. As you can see I am using vbNull which is failing "Invalid use of Null".
How should I test for this?
Private Function CreInvData(ByVal sStoredProcName As String) As Long
Dim oCmd As New ADODB.Command
Dim oParam As ADODB.Parameter
Set oCmd.ActiveConnection = invConn
oCmd.CommandText = sStoredProcName
oCmd.CommandType = adCmdStoredProc
oCmd.Parameters.Append oCmd.CreateParameter("clCode", adVarChar, adParamInput, 4, sClCode)
oCmd.Parameters.Append oCmd.CreateParameter("chgRate", adInteger, adParamInput, , i)
oCmd.Parameters.Append oCmd.CreateParameter("clStRate", adCurrency, adParamInput, , clStRate)
oCmd.Parameters.Append oCmd.CreateParameter("W", adVarChar, adParamInput, 1, W)
oCmd.Parameters.Append oCmd.CreateParameter("Total", adCurrency, adParamReturnValue)
If oCmd.Parameters("Total") = vbNull Then
    total = 0
    total = oCmd.Parameters("Total")
End If 

Open in new window

Avatar of sammySeltzer
Flag of United States of America image

Why not handle this inside your stored procedure?

Something like:

SELECT ISNULL(Total, 0) AS Total

Open in new window

Total gets 0 if it is null.

The way you are using it, you are treating NULL as a value and it is not.
Avatar of Edward Harford
Edward Harford


My Stored Proc is as follows how would I phrase it?
ALTER PROCEDURE [dbo].[zInvCount]
		@clCode nvarchar(4),
		@chgRate int,
		@clStRate float,
		@W nvarchar(1),
		@Val float OUTPUT
 SELECT @Val = SUM((Depth * Height * Width)/100000) * @clStRate from PartInvent WHERE clCode = @clCode and chgRate = @chgRate and WIssued = 0 and Left(Plocation,1) = @W 

Open in new window

Avatar of sammySeltzer
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I am naming "Total" when creating the Return Value parameter:
oCmd.Parameters.Append oCmd.CreateParameter("Total", adCurrency, adParamReturnValue)
The value coming back is Null (or a Currency Value).
As to your suggestion I am using vb6 and I don't think System.dbNull.Value is vb6 syntax. I would have thought vbNull would work but it doesn't for some reason. Is it perhaps the adCurrency datatype I am using?
Thanks for your help.
I have worked it out in Stored Proc. Thanks for your suggestion.