How do I avoid multiple CASTs in sql query?

Question

Hi, I have the following sql query for transforming data but is it possible to save the value of the int in some variable to avoid casting multiple times?

update prospekts set sni_kod = case

when 
    cast(sni_kod as int) >= 1000 and cast(sni_kod as int) <= 1499 
    or cast(sni_kod as int) >= 1600 and cast(sni_kod as int) <= 2439
then '1'
when 
    cast(sni_kod as int) >= 7000 and cast(sni_kod as int) <= 7499 
then 'W'
else
     sni_kod
end

There are a lot more when-cases in the script, just showing the first one. I cannot use anything other than a simple text-script.

Update Using SQL Server 2000

Thanks

Anders

Answer 1

you can use a subquery or CTE:

With xxx AS (
     i_sni_kod = cast(sni_kod as int)
     ...)
UPDATE prospekts set sni_kod = case 
    when i_sni_kod >= 100 ...

Answer 2

Does this work in your DBMS?

update (select prospekts.*, cast(sni_kod as int) sni_kod_int from prospekts)
set sni_kod = case
when 
    sni_kod_int >= 1000 and sni_kod_int <= 1499 
    or sni_kod_int >= 1600 and sni_kod_int <= 2439
then 1
else
     sni_kod
end

Answer 3

This seems like a problem with your model, not your query. Why is that column not simply an int?

This seems like a shoe or glass bottle question. The multiple casting issue you see is simply a result of earlier bad practices. Fix those and your problem will go away.

Answer 4

Something like this might works:

update prospekts set sni_kod = 1
from prospekts
    join (select prospekts.primarykey, cast(prospekts.sni_kod as int) as sni_kod_int from prospekts) p2 on prospekts.primarykey = p2.primarykey
WHERE (p2.sni_kod_int >=1000 and p2.sni_kod_int <= 1499)
    or (p2.sni_kod_int >=1600 and p2.sni_kod_int <= 2439)

Answer 5

The UPDATE statement that you show; I assume it will only be run once? If it is run a second time then the cast to int will fail when it finds the 'W' that was added in the previous run. The best option here is to change the data type of the sni_kod column. Maybe you could explain what this column is holding, why it needs to hold both int and varchar data? Lastly, SQL server is almost certainly only doing the cast once. It is pretty good at finding repeated expressions and sub-queries in a query and only running them once. If you are not sure then take a look at the execution plan.

Answer 6

Is this just a one-off script, as it appears? If so, and you are just trying to save on typing then write as:

update prospekts set sni_kod = case
when 
    xxx >= 1000 and xxx <= 1499 
    or xxx >= 1600 and xxx <= 2439
then '1'
when 
    xxx >= 7000 and xxx <= 7499 
then 'W'
else
     sni_kod
end

... and then do a global search and replace with a text editor.

Or perhaps you are concerned about the performance of casting several times per row when once might do? But again, if this script is a one off, does it really matter?

Answer 7

In you question, you mentioned that you want to "avoid casting multiple times". If you are concerned about performance issues, then don't be. SQL is not converting that field more than once (even though you have it in your script more than once).

Example:

SELECT CONVERT(INT, '123'), CONVERT(INT, '123')

T-SQL is only going to run that method once (no performance loss).

With that said, then the only other concern you could have is typing a bunch... and if that's the case, then the "xxx / find and replace" comment mentioned by Tony Andrews is good enough.

Answer 8

Ok... here's my rewrite of your code...

UPDATE prospekts SET sni_kod = 
    CASE
        WHEN ISNUMERIC(@sni_kod)=1 THEN
            CASE 
                WHEN cast(@sni_kod as int) BETWEEN 1000 AND 1499 OR cast(@sni_kod as int) BETWEEN 1600 AND 2439 THEN '1'
                WHEN cast(@sni_kod as int) BETWEEN 7000 AND 7499 THEN 'W'
                ELSE @sni_kod
            END
        ELSE @sni_kod
    END

This way, it'll only attempt to do a CAST if it's a numeric value, so you won't get cast exceptions, like other people have mentioned in comments.

Since you said there are a lot more statements involved, I'm guessing you have a lot more number ranges that get different values... If that's the case, you might be able to use a second table (can be a temporary one if, like your question says, you're limited to just SQL code) to join on which have min value, max value, and what you want to display based on that. Gets more tricky when you need to evaluate non-numeric values, but it isn't impossible.

Without seeing the full statement, though, this is the best I can offer.

Answer 9

I see someone posted a solution which joins a subquery in the from clause. Here's a solution with -just- a subquery in the from clause.

DECLARE @MyTable TABLE
(
  theKey int identity(1,1) PRIMARY KEY,
  theValue varchar(30)
)
------    
INSERT INTO @MyTable SELECT '1'
INSERT INTO @MyTable SELECT '2'
INSERT INTO @MyTable SELECT '3'
------

UPDATE sub
SET theValue =
  CASE
    WHEN convertedvalue % 2 = 0 THEN 'even'
    ELSE theValue
  END
FROM
(
  SELECT
    CASE
      WHEN Isnumeric(theValue) = 1
      THEN convert(int, theValue)
      ELSE null
    END as convertedValue, *
  FROM @MyTable mt
) as sub
------
SELECT *
FROM @MyTable