Classic ASP Decimal or Numeric OUTPUT Parameters
Recently (unfortunately) I came across a rare (for me anyhoo) instance where I was calling a SQL Server stored procedure from ASP (no ASP.NET available). This particular stored procedure returned RecordSets and one OUTPUT parameter of type decimal. That was the rare part, the decimal output. In ASP.NET this would be more intuitive. However, handling decimal or numeric outputs in ASP isn't documented all that well so I thought I'd review it here.
I'm assuming you have opened a database connection. The next thing to do is create your Command and RecordSet objects...and then set your Command to your open connection. Our example involves a stored procedure so I also set the CommandType and CommandText accordingly.
Set objCommand = Server.CreateObject("ADODB.Command")
Set objRecordSet = Server.CreateObject("ADODB.RecordSet")
Set objCommand.ActiveConnection = objConnection
objCommand.CommandType = adCmdStoredProc
objCommand.CommanyType = "EquipmentHistory_lis"
OK, so far so good. The next step is to add our parameters.
Declare the parameters. In this example I'll pass in a fictitious Customer ID and pass out a fictitious "BalanceTotal".
objCommand.Parameters.Append objCommand.CreateParameter("@pCustomerId", adInteger, adParamInput, , customerId)
objCommand.Parameters.Append objCommand.CreateParameter("@pBalanceTotal", adDecimal, adParamOutput, , 0)
Now comes the fun part. Decimal and Numeric SQL Data types have two extra attributes that are set when created: precision and scale. Precision is the total length of the number of digits and scale is number of digits to the right of the decimal point. Example: a precision of 6 and scale of 2 would store the number 6127.33.
' Set the precision and scale of the decimal output value.
objCommand.Parameters("@pBalanceTotal").Precision = 6 ' Number of places left of the decimal.
objCommand.Parameters("@pBalanceTotal").NumericScale = 2 ' Number of places right of the decimal.
'Execute the command.
Set objRecordSet = objCommand.Execute()
We get back a RecordSet and the output parameter. To get the value from the output parameter, first close the RecordSet (and preferably the Connection). Then the value can be accessed.
' Get the SQL OUTPUT parameter value...the customer's total balance of repairs. Only use if value exists.
Dim balanceTotal
totalCost = 0
If objCommand.Parameters("@pBalanceTotal").Value "" Then
balanceTotal = objCommand.Parameters("@pBalanceTotal").Value
End If
One other note. When returning RecordSets and output parameters, it is a good idea to do SET NOCOUNT ON at the beginning of stored procedure.