471,607 Members | 1,776 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,607 software developers and data experts.

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 1154
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 XIAOLAOHU | last post: by
reply views Thread by MichaelMortimer | last post: by
reply views Thread by CCCYYYY | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.