Calculation Stored Procedures

Calculation stored procedures must always have @OutputValue as the first argument because that is how the result of the calculation is returned back to the database. After that, the number of arguments must match the number of inputs configured for the calculations.

For example,

Fora calculation defined with the following inputs:

The stored procedure arguments should look like:

CREATE PROCEDURE dbo.spLocal_ExportProductionEvent 
@OutputValue		varchar(25) OUTPUT, 
@EventId	int,
@FileName	varchar(100),
@TempPath	varchar(100),
@FilePath	varchar(100) 
AS

If no value is explicitly assigned to the return value, a value of NULL will be returned. If the return value is NULL, the CalculationMgr service will still create/modify a record in the Tests table and set the result to NULL. However, when the output value for a calculation is set to the text string ‘DONOTHING’, then the CalculationMgr will not create a record or modify an existing record’s value. This is an important consideration when implementing high volume calculations as it is desirable to prevent needless records from being inserted in the Tests table.

The @OutputValue should always be set to a varchar(25) to match the format of the Result field in the tests table. The values returned from a calculation go directly into the tests table and are not post-formatted. As such, formatting fields in the Variable sheet (for example, Precision) must be explicitly utilized in the stored procedure if necessary.

For example,

DECLARE	@Value	    float,
        @VarId	    int,
        @Precision  int

SELECT	@Value = 5.12345,
        @VarId = 2

SELECT @Precision = Var_Precision 
FROM Variables
WHERE Var_Id = @VarId

SELECT @OutputValue = ltrim(str(@Value, 25, @Precision))