Avoid problems with data types by using decimal types with numeric scales
Access is a wonderful front end for SQL Server, in particular when you leverage the power of stored procedures with your VBA code. But I recently came across a particularly thorny issue with decimal values.
Decimal Declaration Needed
My problem was using the Float type in my stored procedure:
Alter PROCEDURE sp_SiteQty
@OpID int, -- Operator ID
@SiteID Float, -- Site ID
@Qty int = 0 Output -- Quantity
As
Then I was calling the parameter procedure using the following code:
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cnn
With cmd
.CommandType = adCmdStoredProc
.CommandText = "sp_SiteQty"
.Parameters.Append .CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("OpID", adInteger, adParamInput, , CInt(lngOpID))
'.Parameters.Append .CreateParameter("SiteID", adSingle, adParamInput, , sngSiteID)
.Parameters.Append .CreateParameter("Qty", adInteger, adParamOutput)
.Execute
End With
But the procedure was returning 0 instead of the correct quantity I was looking for. After reading the following article from Microsoft I modified my stored procedure:
Alter PROCEDURE sp_SiteQty
@OpID int, -- Operator ID
@SiteID decimal(5,2), -- Site ID
@Qty int = 0 Output -- Quantity
As
Replacing Float with Decimal data type, (see this article for an excellent comparison of numeric types), and then specifying the numeric scale allowed the stored procedure to return the correct quantity. |
