469,133 Members | 1,179 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,133 developers. It's quick & easy.

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 advance

Jan 2 '07 #1
1 1112
Gonza (go******@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jan 2 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

21 posts views Thread by One Handed Man \( OHM - Terry Burns \) | last post: by
3 posts views Thread by Stimp | last post: by
15 posts views Thread by bill salkin | last post: by
10 posts views Thread by bill | last post: by
1 post views Thread by CARIGAR | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.