Re: Weird Cast operator

From: David Portas (REMOVE_BEFORE_REPLYING_dportas_at_acm.org)
Date: 09/13/04 

Date: Mon, 13 Sep 2004 20:38:06 +0100

CASE can only return a single datatype. In your second CASE one expression
is INT and the other VARCHAR. The rules of datatype precedence give INTEGER
a higher priority than VARCHAR so SQL attempts to convert the string to a
numeric, which causes an error.

Change the second CASE expression to:

    ...
    THEN CAST(CAST(@in AS INT) AS VARCHAR(6))
    ... 

-- 
David Portas
SQL Server MVP
--

Relevant Pages

  • Re: Weird Cast operator
    ... (like in the first sql in my posting), server has to do CAST(@in AS ... INT) in order evaulation the whole expression, ... > CASE can only return a single datatype. ... > is INT and the other VARCHAR. ...
    (microsoft.public.sqlserver.programming)
  • Complicated query
    ... QTopic [varchar 50] ... Term [int] - Validity in months from the current date. ... The problems are to write a query to get the question list for a patient ...
    (microsoft.public.sqlserver.programming)
  • Re: Slow but critical SQL query
    ... you'd want to test the two scenarios of clustering on it ... vs. clustering on CustomerID. ...
    (microsoft.public.sqlserver.programming)
  • Re: what is the speed hit of "SELECT *" with MySql, as opposed to narrower database calls?
    ... >> Are there any benchmarks on how much an extra, unneeded VARCHAR, CHAR, ... >> INT, BIGINT, TEXT or MEDIUMTEXT slows down a database call with MySql? ...
    (comp.lang.php)
  • Re: Sorting by performance difference - int vs. varchar
    ... If they are really integer values, but were stored in varchar by ... > into int. ... >> Sorting the int column will be faster or much faster, ...
    (microsoft.public.sqlserver.programming)