Re: Weird Cast operator

From: Ganesan Rajaraman (ganesanrajaraman_at_gmail.com)
Date: 09/16/04 

Date: 16 Sep 2004 13:22:06 -0700

1. Try making @in string contain '003' (instead of '00-3'. You will
notice that both sqls work. From this, we know that trying to return
int type is not the problem.

2. Now, I guess my question is, even when we have
       CAST(CAST(@in AS INT) AS VARCHAR(6))
(like in the first sql in my posting), server has to do CAST(@in AS
INT) in order evaulation the whole expression, right. Why doesn't it
give a syntax error while trying to do that?

3. We did determine that "Cast(Cast(@in as int) as varchar)" works for
our scenario. But, we are only trying to understand **WHY** one works
while the other doesn't.

-Ganesh

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote in message news:<jsKdnfODHbicatjcRVn-pA@giganews.com>...
> 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))
> ...

Relevant Pages

  • Re: Views vs Stored Procedures, whats the difference?
    ... I'm just trying to differentiate between two fundamentally different SQL objects. ... CREATE TABLE P(pk INT NOT NULL PRIMARY KEY, ... SELECT MAXFROM nestedview ...
    (comp.databases.ms-sqlserver)
  • Re: Weird Cast operator
    ... CASE can only return a single datatype. ... is INT and the other VARCHAR. ... THEN CAST(CAST(@in AS INT) AS VARCHAR) ... SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: Field locked on one SQL record
    ... InspectionNum -- int ... The form is a simple entry form that gives the user an "Inspection Number" ... can only write once to the Note field. ... On the SQL server and in the Access project there are no relationships set ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Views vs Stored Procedures, whats the difference?
    ... In the proc I would write the MAX as you've done. ... @optional_parm1 int = NULL, ... While the above does contain logic, it will give you the best plan in MS SQL ... CREATE TABLE P(pk INT NOT NULL PRIMARY KEY, ...
    (comp.databases.ms-sqlserver)
  • Re: Weird Cast operator
    ... Please refer to @outFails sql from my ... The datatype of this CASE expresion is varchar. ... the value of the CASE expression is int. ... in the SQL datatype precedence list. ...
    (microsoft.public.sqlserver.programming)