473,389 Members | 1,225 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,389 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 1198
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Ken Fine | last post by:
(originally posted to one of macromedia's groups; no help, so hopefully someone here can help me out. I'm using VBScript ASP.) When designing administrative interfaces to websites, we often need...
3
by: ian.proffer | last post by:
We have a 10 digit primary key value in this format: M000123456. The order for this key is first determined by positions 3 and 4 in this example, then positions 1 and 2. So a brief sample of...
9
by: Project2501a | last post by:
hey guys, Question about the internal workings of Access. How are controls added in the Form.Controls collection? I mean, in which order? the order place them on the form? is there a way to...
21
by: One Handed Man \( OHM - Terry Burns \) | last post by:
When using a custom control. In order to check and see if values have changed one has to implement the IPostBackDataCollection interface. The values returned for the control seem to be simply a...
3
by: Stimp | last post by:
I have a listbox of values that I populate from a database. I want the user to be able to re-order the list (by first selecting an item and then clicking 'up' or 'down' buttons) and then save...
15
by: bill salkin | last post by:
I'd like to create a custom error handler like this in VB.NET: .... try ... Throw ("Lender Name not in table") .... catch ("Lender Name not in table")
10
by: bill | last post by:
I have a database of kennels. One page lists the kennels alphabetically by kennel name. I have a field for kennel name and an index with kennel name as the only field. Some of the kennels have...
0
by: pam | last post by:
We have a customer who would like to see their data sorted in a case- insensitive manner. I've tried several of the out of the box collating sequences (identity, system, compatibility) but none of...
15
ajhayes
by: ajhayes | last post by:
I posted a question here last week about an ordering database I'm trying to set up, and got some excellent help. So I'm hoping some of you might be able to get me on the right track with another...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...

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.