Failed to Convert nvarchar to smallint

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Failed to Convert nvarchar to smallint

Author

Topic

ywb
Yak Posting Veteran

55 Posts

Posted - 2012-09-24 : 18:41:27

Hi,

I have a question table that stores questions, which expect answer to be either in text or integer, and an answer table like this:

CREATE TABLE #TempQuestion (queestionID smallint PRIMARY KEY, theType nvarchar(10));
CREATE TABLE #TempAnswer (answerID smallint IDENTITY(1, 1) PRIMARY KEY, questionID smallint, answer nvarchar(10));

INSERT INTO #TempQuestion (queestionID, theType) VALUES (1, 't');
INSERT INTO #TempQuestion (queestionID, theType) VALUES (2, 'i');
INSERT INTO #TempAnswer (questionID, answer) VALUES (1, 'abc');
INSERT INTO #TempAnswer (questionID, answer) VALUES (1, 'bcd');
INSERT INTO #TempAnswer (questionID, answer) VALUES (1, 'cde');
INSERT INTO #TempAnswer (questionID, answer) VALUES (2, '1');
INSERT INTO #TempAnswer (questionID, answer) VALUES (2, '2');
INSERT INTO #TempAnswer (questionID, answer) VALUES (2, '3');

I would like to get all the answers that are integer and are bigger than 2 with this query:

SELECT *
FROM (
SELECT q.*, a.*
FROM #TempQuestion q INNER JOIN #TempAnswer a ON q.queestionID = a.questionID
WHERE (q.theType = 'i')
) AS a
WHERE (CAST(answer AS smallint) >= 2)
ORDER BY queestionID, answerID;

But it failed with this error "Conversion failed when converting the nvarchar value 'abc' to data type smallint."

How can I get around this problem?

Thanks,
ywb

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-24 : 21:04:53

Change your WHERE clause as shown below:

WHERE (CASE WHEN ISNUMERIC(answer) = 1 THEN CAST(answer AS smallint) END >= 2)

HOWEVER:

1. The ISNUMERIC function is not fool-proof. If you had a value of 'e' in a row for answer, it would return isnumeric = 1 but will fail when you try to convert it to smallint.

2. Mixing the two different data types in one column is not a recommended design. Without having thought through it much, at the very least, keep numeric and non-numeric answers in two columns and perhaps have an indicator column which tells you what type of answer is expected.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2012-09-25 : 00:05:08

Enhanced Is Numeric function

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/enhanced-isnumeric-function.aspx

Senthil Kumar C
------------------------------------------------------
MCITP - Database Administration SQL SERVER 2008
MCTS - Database Development SQL SERVER 2008

ywb
Yak Posting Veteran

55 Posts

Posted - 2012-09-25 : 17:50:55

Thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources