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)
oCmd.Execute
If oCmd.Parameters("Total") = vbNull Then
    total = 0
Else
    total = oCmd.Parameters("Total")
End If 

Open in new window

* vb6Microsoft SQL Server

Avatar of undefined
Last Comment
Edward Harford

8/22/2022 - Mon
sammySeltzer

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.
Edward Harford

ASKER
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
AS
BEGIN
 SELECT @Val = SUM((Depth * Height * Width)/100000) * @clStRate from PartInvent WHERE clCode = @clCode and chgRate = @chgRate and WIssued = 0 and Left(Plocation,1) = @W 
	SET NOCOUNT ON;
END

Open in new window

ASKER CERTIFIED SOLUTION
sammySeltzer

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Edward Harford

ASKER
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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Edward Harford

ASKER
I have worked it out in Stored Proc. Thanks for your suggestion.