Is there a more efficient way to order in SQL than when/then statements such that results are in order of where statement?

Question

I'm currently ordering large amounts of integer ID's like so:

when x = a then 1,
when x = b then 2,
...
when x = i then n, for large n

I tried to order by CHARINDEX and then casting what I need as characters:

ORDER BY CHARINDEX(CAST(credit_app.credit_app_id AS CHAR),

followed by the same list of ID's as in my where statement. This is not ordering the same as my where statement.

Does anyone know a more efficient/concise way to generate results in the same order as terms appear in the where statement rather than lines and lines of where/then statements.

Answer 1

Does anyone know a more efficient/concise way to generate results in the same order as terms appear in the where

Yes. Make a table for the order, then join to the table. Now you can order by the value from the new table. This will be way less code, allow for future adjustments to the order without code changes by modifying data in the table, and allow indexes on the order to greatly improve performance. It also sets you up to make it easier to sort this data in the client or a service layer, to reduce memory and CPU use on the database server.

If you are unable to modify the schema, you can still do this via a Table-value constructor VALUES() clause.