Posted by Erland Sommarskog on 01/02/07 13:32

Gonza (gonzalow@gmail.com) writes:
> SELECT ROW_NUMBER() OVER (ORDER BY CASE
> WHEN @expresionOrdenamiento = 1 THEN [Id]
> WHEN @expresionOrdenamiento = 2 THEN [Nombre_RazonSocial]
> WHEN @expresionOrdenamiento = 3 THEN [CUIT_CUIL]
> ELSE null
> END
>...
> The line WHEN @expresionOrdenamiento = 2 THEN [Nombre_RazonSocial] is
> erroring out (the sp compiles, it's a runtime error), i'm getting an
> 'Cannot convert varchar to int'. Maybe it's because Nombre_RazonSocial
> is varchar? If i comment that line then everything works fine. Any
> Help??

The data type from a CASE expression is always the same and is determine
from the strict data-type precedence that SQL Server employs. (See in Books
Online under Datatypes for details). In this case, varchar has lower
precedence than int, so it's converted to int, which then fails.

The remedy is to write:

ORDER BY CASE @x WHEN 1 THEN id WHEN 2 THEN CUIT_CUIL END,
CASE @x WHEN 2 THEN Nombre_RazonSocial END


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]