SQL Server 2008 Field Function - Return 'ok' or 'no' depending on relation between two other fields

Question

I am fairly new to the more advanced database features, such as functions, but I was curious how you would do this in MSSQL (or if it is possible even):

If I have a table and the structure is something like this:

t_test

USR_VALUE   MULTIPLIER   TOLERANCE  VALUE_OK
100         .8           85         OK           
100         .9           85         NO

How would I get VALUE_OK to automatically update itself every time the row is updated depending on the USR_VALUE, MULTIPLIER and TOLERANCE (IE simple calculation: (t_test.USR_VALUE * t_test.MULTIPLIER >= TOLERENCE)? "OK" : "NO")

Answer 1

You want the expression:

CASE
  WHEN USR_VALUE * MULTIPLIER >= TOLERANCE THEN 'OK'
  ELSE 'NO'
END

Note that you can add this to the table as a computed column using...

ALTER TABLE yourtable
ADD VALUE_OK AS     CASE
                      WHEN USR_VALUE * MULTIPLIER >= TOLERANCE THEN 'OK'
                      ELSE 'NO'
                    END

...but be wary of using this value in a WHERE clause where the cost of performing the calculation and lack of index may be prohibitive. Search for indexed calculated columns if you want to take this to the next level.

Answer 2

I actually used Will's code and modified it a bit so it ended up being something similar to Will's, however I can now use it in other fields / tables (created a function):

CREATE FUNCTION [dbo].[is_ok](@Number1 float, @Number2 float, @Tolerence float)
RETURNS varchar(5)
AS
BEGIN
    DECLARE @RETURN varchar(5), @RESULT float;
    SET @RESULT = @Number1 * @Number2;
    IF @Number1 is NULL OR @Number2 is NULL
        SET @RETURN = '';
    ELSE IF @RESULT >= @Tolerence
        SET @RETURN = 'OK';
    ELSE
        SET @RETURN = 'NO';
    RETURN @RETURN;
END;

then added the new column:

ALTER TABLE t_test ADD [value_ok] AS ([dbo].[is_ok]([user_value],[multiplier],[tolerance]));

(this should work, but I did copy it from the test database I was using and modified the field names to match the question's example table)