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

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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.

Edward Harford

ASKER

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