September 26, 2008 at 4:20 pm
Hi,
I am passing xml from .Net and using that xml i am trying to execute some sql statements.
STEP1
On execution of following query i am getting some results
SELECT
*
FROM OPENXML (@TRANSACTION_HANDLE, '/Data',1)
WITH (
A VARCHAR(20) 'A',
B VARCHAR(20) 'B',
C VARCHAR(10) 'C',
D VARCHAR(10) 'D',
E MONEY 'E'
)
WHERE ISNUMERIC(A) = 1
Following are the results of query.
[p]
4077016 WE091608 630116819/23/2008198.38
5513416012008WE092108 630116839/23/200888.08
5558714012008WE091108 630115819/17/200821.37
3979616 9080906128 630116499/15/2008957.12
4099326 9080906128 630116499/15/2008117.49
4098326 34071 630115928/30/20081245.44
4093910 WE091608 630116439/22/2008808.53
5546814012008WE082608 630115589/15/2008228.68
5572232012008WE081408 630116719/23/2008611.73
54856230120081684070 630117188/26/2008904.9
5486014012008WE081508 630115689/15/2008644.55
9190 11181 630115555/29/20083500
9189 SPONSORSHIP 630116539/11/200812500
9189 2008-08 630116728/28/20081790
5343716012008TBG2585 630116739/2/2008 19.9
[/p]
declare @MAX_ID NUMERIC(20)
SET @MAX_ID =999999
In next step i executed following query
STEP2
SELECT
*
FROM
(
SELECT
*
FROM OPENXML (@TRANSACTION_HANDLE, '/Data',1)
WITH (
A VARCHAR(20) 'A',
B VARCHAR(20) 'B',
C VARCHAR(10) 'C',
D VARCHAR(10) 'D',
E MONEY 'E'
)
WHERE ISNUMERIC(A) = 1
)MYDATA
WHERE ISNUMERIC(A)=1 AND A < @MAX_ID
It executed successfully and returned follwoing results
9190 11181 630115555/29/20083500
9189 SPONSORSHIP 630116539/11/200812500
9189 2008-08 630116728/28/20081790
STEP3
If i run the next query i am getting "Error converting varchar to numeric"
SELECT
*
FROM
(
SELECT
*
FROM OPENXML (@TRANSACTION_HANDLE, '/Data',1)
WITH (
A VARCHAR(20) 'A',
B VARCHAR(20) 'B',
C VARCHAR(10) 'C',
D VARCHAR(10) 'D',
E MONEY 'E'
)
WHERE ISNUMERIC(A) = 1
)MYDATA
WHERE A < @MAX_ID AND ISNUMERIC(A)=1
Only difference between step2 and step3 is in where clause A < @MAX_ID , ISNUMERIC(A)=1 conditions are interchanged
What could be the reason for this?
September 26, 2008 at 4:32 pm
That's called short-circuiting. Sql server will stop evaluating the where condition(s) as soon as the whole statement cannot be true.
Run this and you will see what I mean. The first 3 statements work return true or false, but the last one errors out because the isnumeric() is evaluated last... hence causing the error. You will also notice that a preceding statement is not erroring out because the server doesn't evaluate the condition where it would fail :
DECLARE @a AS CHAR(1)
SET @a = '0'
IF ISNUMERIC(@a) = 1 AND @a < 10
PRINT 'OK'
ELSE
PRINT 'FAIL'
GO
DECLARE @a AS CHAR(1)
SET @a = 'Z'
IF ISNUMERIC(@a) = 1 AND @a < 10
PRINT 'OK'
ELSE
PRINT 'FAIL'
GO
DECLARE @a AS CHAR(1)
SET @a = '0'
IF @a < 10 AND ISNUMERIC(@a) = 1
PRINT 'OK'
ELSE
PRINT 'FAIL'
GO
DECLARE @a AS CHAR(1)
SET @a = 'Z'
IF @a < 10 AND ISNUMERIC(@a) = 1
PRINT 'OK'
ELSE
PRINT 'FAIL'
September 26, 2008 at 4:38 pm
As a side note, isnumeric has many gotchas... those string are all considered valid numeric values :
'1'
'1.00'
'1E10'
You might consider using this if you need something that checks for IsAllNumbers :
DECLARE @a VARCHAR(15)
SET @a = '0123456789'
IF @a NOT LIKE '%[^0-9]%'
PRINT @a + ' Is all numbers'
ELSE
PRINT @a + ' This contains non-numbers'
SET @a = '0123456789e'
IF @a NOT LIKE '%[^0-9]%'
PRINT @a + ' Is all numbers'
ELSE
PRINT @a + ' This contains non-numbers'
September 28, 2008 at 10:32 am
Thats right. Point here is i used derived table, where in my inner query return only the rows which are having numeric values in column A. So in outer query there is no chance of getting non numeric data.
Even then i am getting exception . The only option i left with is use character comparision rather than numeric comprasion , which may get some unwaned data.
Thanks
Srikanth
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply