Ruben Costers Ruben Costers - 3 months ago 7
SQL Question

How to iterate efficiently through a TSQL variable Table

I have searched quite extensivly for the answer to my question but without succes. To be honest, I don't really know what the solution would look like, hence difficult to make a good search.

So, I have declared a table variable at the start of my Stored Procedure. This table gets some static data from another table. This way I don't always have to reload the original table (correct me if I'm wrong please).
This table contains 6 columns; A, B, C, D, E, F (30, 60, 90, 180, 360, 9999).

It looks somehting like this right now:

DECLARE @VALUES TABLE (A INT, B INT, C INT, D INT, E INT, F INT);

INSERT INTO @VALUES(A, B, C, D, E, F)
SELECT A, B, C, D, E, F
FROM SOME_TABLE as RV
WHERE ID = 1;


Now I want to iterate through this table to match a value with the correct table. I can use IF statements but that seems like it is more work then needed and not really performance minded.
I will give an example:

My value X equals 61. I want to match this with the correct column (A, B, C, ...). For this number it would be colomn C, since 61 is larger then 60 (B) and smaller then 90 (C).

Everything smaller then 30 goes into A, smaller then 60 -> B, ...

Can anyone help me with this? And tell me where to look for the must optimal solution to my problem.

Thanks in advance!

Answer

You can do this using outer apply:

select top 1 v.*
from @values val outer apply
     (values (A, 'A'), (B, 'B'), (C, 'C'), (D, 'D'), (E, 'E'), (F, 'F')
     ) v(val, name)
where val <= @YourValue
order by val desc;

The advantage of this approach is that it is easy to include the column name as well as the value.

Comments