How to refactor this sql query

Question

Hi, I have a lengthy query here, and wondering whether it could be refactor?

Declare @A1 as int
Declare @A2 as int
...
Declare @A50 as int

SET @A1 =(Select id from table where code='ABC1')
SET @A2 =(Select id from table where code='ABC2')
...
SET @A50 =(Select id from table where code='ABC50')

Insert into tableB
Select 
Case when @A1='somevalue' Then 'x' else 'y' End,
Case when @A2='somevalue' Then 'x' else 'y' End,
..

Case when @A50='somevalue' Then 'x' else 'y' End
From tableC inner join ......

So as you can see from above, there is quite some redundant code. But I can not think of a way to make it simpler.

Any help is appreciated.

Answer 1

I am not entirely sure from your example, but it looks like you should be able to do one of a few things.

1. Create a nice look up table that will tell you for a given value of the select statement what should be placed there. This would be much shorter and should be insanely fast.

2. Create a simple for loop in your code and generate a list of 50 small queries.

3. Use sub-selects or generate a list of selects with one round trip to retrieve your @a1-@A50 values and then generate the query with them already in place.

Jacob

Answer 2

If you need the variables assigned, you could pivot your table...

SELECT *
FROM 
(
SELECT Code, Id
FROM Table
) t
PIVOT
(MAX(Id) FOR Code IN ([ABC1],[ABC2],[ABC3],[ABC50])) p /* List them all here */
;

...and then assign them accordingly.

SELECT @A1 = [ABC1], @A2 = [ABC2]
FROM 
(
SELECT Code, Id
FROM Table
) t
PIVOT
(MAX(Id) FOR Code IN ([ABC1],[ABC2],[ABC3],[ABC50])) p /* List them all here */
;

But I doubt you actually need to assign them at all. I just can't really picture what you're trying to achieve.

Pivotting may help you, as you can still use the CASE statements.

Rob

Answer 3

Without taking the time to develop a full answer, I would start by trying:

select id from table where code in ('ABC1', ... ,'ABC50')

then pivot that, to get one row result set of columns ABC1 through ABC50 with ID values.

Join that row in the FROM.

If 'somevalue', 'x' and 'y' are constant for all fifty expressions. Then start from:

select case id when 'somevalue' then 'x' else 'y' end as XY 
from table 
where code in ('ABC1', ... ,'ABC50')