Nested Case Statements in SQL 2005 Function?

The sql function in the the snippets window gives "green" for ALL rows that call it in an insert/update/select statement. The insert pulls rows from one table to another and doing a calculation (this function) to fill a column in the destination table that's not in the original table. But in being updated, the second table gets "green" in all rows, even those where the ration of progress/target is not >=85%. So i figure it's setting @MonthProgressPercent to the input of first row considered, namely 95/100, and not adjusting it's value for every call. Or i could be smoking crack....Is a nested case statement possible? Am i going about this all wrong? How can i get the variable set in the first case statement, namely @monthprogresspercent, to change and be considered anew each time going into the second case statement, all within one function?


Start Free Trial

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:

ALTER FUNCTION [dbo].[fn_GetMonthColorIndicator] (@MonthProgress int, @MonthTarget int)
 
RETURNS SQL_VARIANT
AS
BEGIN
DECLARE @MonthColorIndicator SQL_VARIANT, @MonthProgressPercent SQL_VARIANT
 
SET @MonthProgressPercent = (
CASE
WHEN @MonthProgress = 0 OR @MonthTarget = 0 THEN 0
ELSE (CAST(@MonthProgress AS FLOAT)/CAST(@MonthTarget AS FLOAT)*100)
END 
)
 
SET @MonthColorIndicator = (				
CASE 
WHEN @MonthProgressPercent >='85' THEN 'Green'
WHEN @MonthProgressPercent >='60' THEN 'Yellow'
WHEN @MonthProgressPercent >'0' THEN 'Red'
ELSE 'Green' --if zero, then also green (target is zero)
END
		)
RETURN (SELECT @MonthColorIndicator) --gives same indicator for all rows!!!
--RETURN (SELECT @MonthProgressPercent) --will return correct calculation for each insert/update call to this function.
 
END