We help IT Professionals succeed at work.

Testing for Null in Stored Proc Return Value

eharford
eharford asked
on
84 Views
Last Modified: 2017-03-25
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

Comment
Watch Question

Top Expert 2011

Commented:
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.

Author

Commented:
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

Top Expert 2011
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
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.

Author

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