March 13, 2009 at 9:51 am
Please post the DDL for your table, sample data (as INSERT statements that can be cut and paste into SSMS and run) including both numeric and non-nummeric client ids, and the expected output based on the sample data.
This will help us help you (see the article in my signature block below regarding asking for assistance).
March 13, 2009 at 9:55 am
ramadesai108 (3/13/2009)
If I run the proc with param 'Sp1' then i get the following:Conversion failed when converting the nvarchar value 'Sp1' to data type int.
mister boom was on the right track with this - you are mixing up datatypes in the result of the case statement - you can't do this.
Give us some sample data for your table, and a definitve requirement and we can help..
Kev
March 13, 2009 at 9:56 am
As I said, your CASE statement is returning a mix of integers and strings.
A resolved version of your CASE statement is:
SELECT
CASE
WHEN 0 <> 0
THEN (CASE WHEN 1 = 1
THEN 1
ELSE 'text' END)
ELSE 'text' END
That returns 'Conversion failed when converting the varchar value 'text' to data type int', as I expected. However, for some reason, if you change the first 0 to 1, the query works, returning 1. If anyone can explain why that works, I'd be grateful!
I think you'll need to find a different way to sort your results. I don’t have your data to test this with, but how about something like this:
SELECT intID, ClientID
FROMClients
WHEREintID = @intID
ORDER BY intID,
CASE WHEN CHARINDEX('-',ClientID) <> 0 THEN
(CASE WHEN ISNUMERIC(SUBSTRING(ClientID ,CHARINDEX('-',ClientID ) + 1, 5)) = 1
THEN CONVERT(INT,SUBSTRING(ClientID ,CHARINDEX('-',ClientID ) + 1, 5))
ELSE 0 END) ELSE 0 END DESC,
ClientID ASC
March 13, 2009 at 10:14 am
Hi,
The index is on the intID column. here is the script to create table and add data
create table Clients
(
intID INT,
ClientID VarChar(10)
)
GO
insert into Clients Values(1,'Sp1')
insert into Clients Values(2,'Sp20')
insert into Clients Values(3,'Sp2')
insert into Clients Values(4,'0101-1')
insert into Clients Values(5,'0101-12')
insert into Clients Values(6,'0101-2')
GO
CREATE PROCEDURE [dbo].[sp_getClients]
AS
BEGIN
SELECT intID, ClientID
FROM Clients
WHERE intID = @intID
ORDER BY intID,
CASE WHEN CHARINDEX('-',ClientID) <> 0 THEN
(CASE WHEN ISNUMERIC(SUBSTRING(ClientID ,CHARINDEX('-',ClientID ) + 1, 5)) = 1
THEN CONVERT(INT,SUBSTRING(ClientID ,CHARINDEX('-',ClientID ) + 1, 5))
ELSE ClientID END)
ELSE ClientID END
END
GO
March 13, 2009 at 10:22 am
mister boom (3/13/2009)
That returns 'Conversion failed when converting the varchar value 'text' to data type int', as I expected. However, for some reason, if you change the first 0 to 1, the query works, returning 1. If anyone can explain why that works, I'd be grateful!
CASE returns the highest precedence type from the set of all the possible results. int is much higher precedence than char/varchar ( see BOL or http://msdn.microsoft.com/en-us/library/ms190309(SQL.90).aspx )
So when you change the first 0 to 1 and the answer is '1' then that matches the data type that the case statement is expecting. Leaving it as 0, means the result should be 'text' but that can't implicitly convert to int, so the error is raised.
Kev
March 13, 2009 at 10:34 am
ramadesai108 (3/13/2009)
Hi,The index is on the intID column. here is the script to create table and add data
create table Clients
(
intID INT,
ClientID VarChar(10)
)
GO
insert into Clients Values(1,'Sp1')
insert into Clients Values(2,'Sp20')
insert into Clients Values(3,'Sp2')
insert into Clients Values(4,'0101-1')
insert into Clients Values(5,'0101-12')
insert into Clients Values(6,'0101-2')
GO
CREATE PROCEDURE [dbo].[sp_getClients]
AS
BEGIN
SELECT intID, ClientID
FROM Clients
WHERE intID = @intID
ORDER BY intID,
CASE WHEN CHARINDEX('-',ClientID) <> 0 THEN
(CASE WHEN ISNUMERIC(SUBSTRING(ClientID ,CHARINDEX('-',ClientID ) + 1, 5)) = 1
THEN CONVERT(INT,SUBSTRING(ClientID ,CHARINDEX('-',ClientID ) + 1, 5))
ELSE ClientID END)
ELSE ClientID END
END
GO
Okay, but what is the expected output based on the sample data?
March 13, 2009 at 10:45 am
ramadesai108 (3/13/2009)
If I run the proc with param 'Sp1' then i get the following:Conversion failed when converting the nvarchar value 'Sp1' to data type int.
This could be a different issue to the one described (output of the CASE being more than one datatype). The stored procedure definition has no parameters.
Also: values in the table are:
insert into Clients Values(1,'Sp1')
insert into Clients Values(2,'Sp20')
insert into Clients Values(3,'Sp2')
insert into Clients Values(4,'0101-1')
insert into Clients Values(5,'0101-12')
insert into Clients Values(6,'0101-2')
and the WHERE clause in the stored procedure is:
WHERE intID = @intID
but the value passed in to the (nonexistent) parameter is a ClientID value 'Sp1', not an integer intID value expected by the WHERE.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 13, 2009 at 10:46 am
The output should be intID and ClientID. Take out the intID in the order by statement because that was a mistake. As seen by one of the responder, the query fails and complains about conversion. How do I fix it? and how can I avoid having the case statement inside the order by?
Thanks.
March 13, 2009 at 11:12 am
ramadesai108 (3/13/2009)
The output should be intID and ClientID. Take out the intID in the order by statement because that was a mistake. As seen by one of the responder, the query fails and complains about conversion. How do I fix it? and how can I avoid having the case statement inside the order by?Thanks.
Last time asking, please show what the output should look like based on the sample data. I'm not going to guess what it should look like based on your description of the output.
March 13, 2009 at 11:51 am
ramadesai108
Your sample data is unique. Is the data in your database unique? If so, no you don't need to use an order by clause as your stored proc will only return one row.
If your data isn't unique, what are you trying to find with your stored proc? As Chris pointed out, your code tries to match on intID but you are passing ‘Sp1” which is a clientID. Are you trying to find all intIDs for a specified clientID or are you trying to find all clientIDs for a specified intID?
If you can explain what this is code is meant to do, it may help us to help you more quickly.
March 13, 2009 at 12:26 pm
I apologize for the typo, here is the changed proc with ClientID as the parameter
CREATE PROCEDURE [dbo].[sp_getClients]
@ClientID VarChar(20) = 'Sp1'
AS
BEGIN
SELECT intID, ClientID
FROM Clients
WHERE ClientID = @ClientID
ORDER BY intID,
CASE WHEN CHARINDEX('-',ClientID) <> 0 THEN
(CASE WHEN ISNUMERIC(SUBSTRING(ClientID ,CHARINDEX('-',ClientID ) + 1, 5)) = 1
THEN CONVERT(INT,SUBSTRING(ClientID ,CHARINDEX('-',ClientID ) + 1, 5))
ELSE ClientID END)
ELSE ClientID END
END
GO
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply