Error converting data type varchar to numeric
-
I am getting error while i run query. Client id is numeric 18,0 when i run with parameter with ALL it is working, It is in statement
DECLARE @clientid varchar(20) = '2,3,4'
SELECT [TestID],
[CLientid]
FROM [dbo].TEST
WHERE Clientid IN ( CASE cast( @clientid as varchar(10) )
WHEN 'ALL' THEN Clientid
ELSE cast( @clientid as varchar(10)
END )
-
sks_989 (4/5/2016)
I am getting error while i run query. Client id is numeric 18,0 when i run with parameter with ALL it is working, It is in statementDECLARE @clientid varchar(20) = '2,3,4'
SELECT [TestID],
[CLientid]
FROM [dbo].TEST
WHERE Clientid IN ( CASE cast( @clientid as varchar(10) )
WHEN 'ALL' THEN Clientid
ELSE cast( @clientid as varchar(10)
END )
You are trying to return a column which is either numeric or varchar, depending on the conditions. Change things around so that the data type for the column is the same, for all conditions.
Also, this looks like an unusual way of searching for something. If you explain what you are trying to do, we may be able to find a better way.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
sks_989 (4/5/2016)
I am getting error while i run query. Client id is numeric 18,0 when i run with parameter with ALL it is working, It is in statementDECLARE @clientid varchar(20) = '2,3,4'
SELECT [TestID],
[CLientid]
FROM [dbo].TEST
WHERE Clientid IN ( CASE cast( @clientid as varchar(10) )
WHEN 'ALL' THEN Clientid
ELSE cast( @clientid as varchar(10)
END )
There are a few problems with this, but I'll focus on the big one.
It looks like you're expecting to be able to pass the comma-delimited list assigned to @clientid directly to the IN operator.
That's not how IN works. Even if the conversion directly from the string '2,3,4' to numeric could succeed (it can't), it will treat @clientid as a single value, and look for rows where the value of the Clientid column equals '2,3,4'.
You'll need to either insert those values into a temporary table and join to it or split the string.
As for the exact error you're getting right now, CASE returns a value with the datatype with highest precedence from the datatypes of its possible return expressions. Here that's numeric, so CAST(@clientid as varchar(10)) will be converted to numeric. Since '2,3,4' cannot be converted to numeric, you get that error.
Of course, that's not the main issue, but I figured I'd include it for completeness.
Cheers!
-
Looks like he's trying to pass multiple values into a stored procedure without using a TVF, or something like DelimitedSplit8K().
This worked for me:
USE tempdb;
GO
CREATE TABLE test (testID int identity,
clientID int);
GO
INSERT INTO test(clientID) VALUES (1),(2),(3);
/* grossly simplified parameters */
DECLARE @Values VARCHAR(10) = '2,3,4';
SELECT *
FROM SCRIDB.dbo.DelimitedSplit8K(@Values,','); /* my splitter is in my SCRIDB database... */
-
Yes i am trying to use IN Operation with client id as list. '2,3,4' so how you can do that without creating temp table. If i put 'ALL' it works because i said clientid in ( clientid)
-
If you don't want to use temporary objects and/or change how the values in the criteria are passed, then you'll need to split the string with the splitter of your choice. pietlinden gave one example of this using DelimitedSplit8k, which is a very efficient T-SQL splitter.
Some quick googling and/or searching of this site should give you plenty of info on DelimitedSplit8k.
For the record, yes, it is expected that it will work when that first branch of the CASE expression is true, because then you're just doing IN (single_value), and the data type is correct.
For that branch, you're just doing
clientid IN (clientid)which is just
clientid=clientidThe problematic branch does this
clientid IN (@clientid)in this case is the same as
clientid IN ('2,3,4')which is the same as
clientid='2,3,4'What you're wanting to get is this:
clientid IN (2,3,4)which is equivalent to
clientid=2 OR clientid=3 OR clientid=4Without using a TVP, you'll need to use a splitter as mentioned above (technically there is another option with dynamic T-SQL, but I would avoid that unless I had really good reasons to prefer it in a particular case).
Cheers!
-
sks_989 (4/5/2016)
Yes i am trying to use IN Operation with client id as list. '2,3,4' so how you can do that without creating temp table. If i put 'ALL' it works because i said clientid in ( clientid)There are several ways, but if you insist on using a delimited string, read the following article and the example shown by pietlinden.
http://www.sqlservercentral.com/articles/Tally+Table/72993/
Another option is to use table valued parameters
-
Thanks everyone input i was able to do from creating function
CREATE FUNCTION SplitString
(
@Input NVARCHAR(MAX),
@Character CHAR(1)
)
RETURNS @Output TABLE (
Item NVARCHAR(1000)
)
AS
BEGIN
DECLARE @StartIndex INT, @EndIndex INT
SET @StartIndex = 1
IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
BEGIN
SET @Input = @Input + @Character
END
WHILE CHARINDEX(@Character, @Input) > 0
BEGIN
SET @EndIndex = CHARINDEX(@Character, @Input)
INSERT INTO @Output(Item)
SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)
SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
END
RETURN
END
GO
And then
SELECT clientid , Testid
FROM [dbo].Test
WHERE clientid IN (
SELECT CAST(Item AS INTEGER)
FROM dbo.SplitString('2,3', ',')
)
-
sks_989 (4/5/2016)
Thanks everyone input i was able to do from creating functionCREATE FUNCTION SplitString
(
@Input NVARCHAR(MAX),
@Character CHAR(1)
)
RETURNS @Output TABLE (
Item NVARCHAR(1000)
)
AS
BEGIN
DECLARE @StartIndex INT, @EndIndex INT
SET @StartIndex = 1
IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
BEGIN
SET @Input = @Input + @Character
END
WHILE CHARINDEX(@Character, @Input) > 0
BEGIN
SET @EndIndex = CHARINDEX(@Character, @Input)
INSERT INTO @Output(Item)
SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)
SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
END
RETURN
END
GO
And then
SELECT clientid , Testid
FROM [dbo].Test
WHERE clientid IN (
SELECT CAST(Item AS INTEGER)
FROM dbo.SplitString('2,3', ',')
)
This is known as a scalar function with looping and is likely to perform terribly. If you have no cares about performance, that's fine, but you should know that this is a bad idea.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Thanks for the performance thing if i put into temp table and then join to that table it should be good on performance wise.
-
sks_989 (4/6/2016)
Thanks for the performance thing if i put into temp table and then join to that table it should be good on performance wise.Eventually, it won't be. While loop and rCTE splitters have relatively terrible performance and resource usage.
From http://www.sqlservercentral.com/articles/Tally+Table/72993/
"Good enough" usually isn't, in the long run.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply