1. Normalise your data.

Here I do normalisation on-fly, but you better implement it in the table design:

SELECT ID, 1, Value1, Value1_Pct

FROM #DataTableName

UNION

SELECT ID, 2, Value2, Value2_Pct

FROM #DataTableName

UNION

SELECT ID, 3, Value3, Value3_Pct

FROM #DataTableName

UNION

SELECT ID, 4, Value4, Value4_Pct

FROM #DataTableName

2. Get rid of UPDATE altogether.

Storing CheckNAme in the base table won't add much, as indexing would be ineffective, and I suspect you have those values updated quite often, so you need to refresh those checks quite regularly.

Make it into a view, or use as a derived table (aka CTE) in your queries:

SELECT T.ID, T.ValueID, T.Value, T.Value_PCT, J.CheckName

FROM (

SELECT ID, 1, Value1, Value1_Pct

FROM #DataTableName

UNION

SELECT ID, 2, Value2, Value2_Pct

FROM #DataTableName

UNION

SELECT ID, 3, Value3, Value3_Pct

FROM #DataTableName

UNION

SELECT ID, 4, Value4, Value4_Pct

FROM #DataTableName

) T (ID, ValueID, Value, Value_PCT)

LEFT JOIN #Join J ON (Value_Pct >= J.Low OR J.Low IS NULL)

AND (Value_Pct <= J.High OR J.High IS NULL)

As you can see, data normalisation reduces the amount of necessary code by 4 times.

Comes quite useful at times. 🙂