this might help :

select * from (
   select *, row_number() over (order by abs(weight - 123)) rn
   from fg.CostTest
   where zone = 'A'
) t where rn = 1

I suggest making sure your columns zone and weight has index which would definitely help in the performance.

Then if the table is going to be used several times in queries or stored procedures, it’s best to create an indexed view that contains the LowerBoundWeight and Weight, and then use that view in queries as a join.

The View would be similar to the below:

CREATE VIEW vw_CostTest
AS
SELECT *, LAG(Weight,1,0) OVER (partition by Zone ORDER BY Weight) LowerBoundWeight
FROM CostTest;

Then on the select, join it as such

SELECT [COLUMNA_Names]
FROM Table1 t1
    JOIN vw_CostTest ct on t1.zone=ct.zone 
        AND t1.weight > ct.LowerBoundWeight
        AND t1.weight <= ct.weight

Create procedure, it will help for repetition query :

Step 1 :

Create PROCEDURE [dbo].[FindCost]
 @Zone char(1),
 @Weight int
AS
BEGIN
 SET NOCOUNT ON;
 SELECT Top 1 *
 FROM costtest
 WHERE Zone = @Zone 
 AND Weight <= @Weight
ORDER BY Weight DESC

Step 2: Try the store procedure

Exec FindCost 'A',123