Error converting data type nvarchar to numeric -- sql server programming

michael
6/9/2007 7:21:02 PM

Thanks for looking into this. The integrity of the table is fine. In a
nutshell, here's how to repro this on SQL 2005.

Make a table Test with one column, Code nvarchar(255). Populate it with 1,
2, 3, 4, 5, 6, 7, 8, A9

Run this Query:

SELECT Code
FROM [Test]
WHERE (CONVERT(decimal(18,2), Code) >= 5) AND (CONVERT(decimal(18,2),
Code) < 10) And Code like '[^A-Z]%'

Do you see it too?

--
Michael

[quoted text, click to view]

Erland Sommarskog
6/9/2007 10:17:15 PM

michael (michael@discussions.microsoft.com) writes:
[quoted text, click to view]

Indeed strange. Smells like a bug. Or corruption. Did you run
DBCC CHECKTABLE on the table?

If DBCC comes out clean, you could try this to track down the trouble-
some row:

SELECT id = identity(int, 1, 1), * INTO Copy FROM CodeTable

Then run:

SELECT CONVERT(decimal(18, 2), Code) AS ConvertedCode
FROM Copy
WHERE (CONVERT(decimal(18, 2), Code)>=800.00)

to verify that the problem remains in the copy.

If it does, run:

declare @id int
declare copy_cur CURSOR LOCAL STATIC FOR
SELECT id FROM Copy
OPEN copy_cur

WHILE 1 = 1
BEGIN
FETCH copy_cur INTO @id
IF @@fetch_status <> 0
BREAK

SELECT CONVERT(decimal(18, 2), Code) AS ConvertedCode
FROM Copy
WHERE CASE id WHEN @id
THEN CONVERT(decimal(18, 2), Code)
ELSE 200
END >= 800.00
END
DEALLOCATE copy_cur

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

Roy Harvey
6/10/2007 12:00:00 AM

The problem is that you can not control the order of the operations,
and the conversion that fails happens before the test that prevents
the failure.

It is possible, though a bit confusing, to use a CASE expression to
work around this, but I am not absolutely certain it is guaranteed to
work.

SELECT Code
FROM [Test]
WHERE CASE WHEN Code like '[A-Z]%'
THEN 0
WHEN (CONVERT(decimal(18,2), Code) < 5)
THEN 0
WHEN (CONVERT(decimal(18,2), Code) >= 10)
THEN 0
ELSE 1
END = 1

Roy Harvey
Beacon Falls, CT

On Sat, 9 Jun 2007 19:21:02 -0700, michael
[quoted text, click to view]

michael
6/10/2007 11:28:02 AM

I suspected that it had something to do with the order of execution. Oddly,
it doesn't occur when I run it on a SQL 2000 instance.

Thanks for your insight.
--
Michael

[quoted text, click to view]

michael
6/10/2007 3:36:02 PM

I certainly appreciated the time you took to create the post. It's equally
difficult to extract the essence of your problem (or what you think is the
essence) and turn it into a readable post, one that may attrack a response.
Sometimes things get left out. Actually, I got the inspiration to getting at
the actual problem came from reading your initial post in the first place.

Again, I hope my oversight can be forgiven. It's the things that you think
are most innocuous that all too often turn out to be the problem.

--
Michael

[quoted text, click to view]

Erland Sommarskog
6/10/2007 9:45:41 PM

michael (michael@discussions.microsoft.com) writes:
[quoted text, click to view]

Consider it pure luck. Since this is not deterministic, there is after
all a fifty-fifty chance that you get away with it.

Permit also to say, that I would have appreciated if you had posted the
correct query in your first post. The query you had there, should not
produce the error you posted.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : Error converting data type nvarchar to numeric