CODEWITHSUNDEEP
sqldatabaset-sql
Ruben Costers
Ruben Costers

Reputation: 23

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!

Upvotes: 0

Views: 113

Answers (4)

user5226582
user5226582

Reputation: 1986

Instead of having a single column table with delimiters, you could define table with ranges:

DECLARE @VALUES TABLE (Name VARCHAR(20), Min INT, Max INT);

INSERT INTO @VALUES(Name, Min, Max)
SELECT 'Category 1' As Name, 0 as Min, A as Max
FROM SOME_TABLE WHERE ID = 1;

INSERT INTO @VALUES(Name, Min, Max)
SELECT 'Category 2' As Name, A as Min, B as Max
FROM SOME_TABLE WHERE ID = 1;

INSERT INTO @VALUES(Name, Min, Max)
SELECT 'Category 3' As Name, B as Min, C as Max
FROM SOME_TABLE WHERE ID = 1;
...

Then you could join on this table:

SELECT X.X, V.Name
FROM XValues X JOIN @VALUES V on X.X >= V.Min AND X.X < V.Max

Upvotes: 3

LukStorms
LukStorms

Reputation: 29647

If you only need to calculate 1 value against that table, then you can set it directly with a select.

For example: 

declare @value int = 61;
declare @classvalue int;

set @classvalue = (select top 1
(case 
 when @value > E then F
 when @value > D then E
 when @value > C then D
 when @value > B then C
 when @value > A then B
 when @value > 0 then A
 else null
 end) as classvalue
from @VALUES);

select @classvalue;

Actually, in that example you could directly select from the table instead of first putting the record in @VALUES.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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.

Upvotes: 0

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

The question is quite unclear and the whole approach smells... If you are fix with columns A to F (and your question seems to be like this), you might go this way:

DECLARE @SomeValues TABLE(Val INT);
INSERT INTO @SomeValues VALUES(1),(30),(61),(180),(181);

DECLARE @VALUES TABLE (A INT, B INT, C INT, D INT, E INT, F INT);
INSERT INTO @VALUES VALUES(30, 60, 90, 180, 360, 9999);

SELECT sv.Val
      ,CASE WHEN sv.Val<=v.A THEN v.A
            WHEN sv.Val<=v.B THEN v.B
            WHEN sv.Val<=v.C THEN v.C
            WHEN sv.Val<=v.D THEN v.D
            WHEN sv.Val<=v.E THEN v.E
            WHEN sv.Val<=v.F THEN v.F END AS Corresponding
FROM @SomeValues AS sv
CROSS JOIN @VALUES AS v

update

You might return the column's name instead of the value by using THEN 'A' instead of THEN v.A (and further down...)

Upvotes: 1

Related Questions