Custom Ordering question

Hi group, i'm having a problem with custom ordering in a SP. My code is
the following:

CREATE PROCEDURE [dbo].[PersonasSelectAll_P]
@.fromRow int,
@.toRow int,
@.expresionOrdenamiento int = null
AS
SET NOCOUNT ON

DECLARE @.TotalFilas int

Select @.TotalFilas = count(*) from [Personas]

SELECT * FROM
(
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
) AS Row
, * , @.TotalFilas as TotalRows
FROM [Personas]
AS NumberedPersons
WHERE
(Row >= isnull(@.fromRow, 0)) AND (Row <= isnull(@.toRow, row))

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??

Thanks in advanceGonza (gonzalow@.gmail.com) writes:

Quote:

Originally Posted by

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx