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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Failed to Convert nvarchar to smallint
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Yak Posting Veteran

55 Posts

Posted - 09/24/2012 :  18:41:27  Show Profile  Reply with Quote

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 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?


Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/24/2012 :  21:04:53  Show Profile  Reply with Quote
Change your WHERE clause as shown below:
WHERE (CASE WHEN ISNUMERIC(answer) = 1 THEN CAST(answer AS smallint) END >= 2)

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.
Go to Top of Page

Flowing Fount of Yak Knowledge

1007 Posts

Posted - 09/25/2012 :  00:05:08  Show Profile  Visit senthil_nagore's Homepage  Send senthil_nagore a Yahoo! Message  Reply with Quote
Enhanced Is Numeric function

Senthil Kumar C
MCITP - Database Administration SQL SERVER 2008
MCTS - Database Development SQL SERVER 2008
Go to Top of Page

Yak Posting Veteran

55 Posts

Posted - 09/25/2012 :  17:50:55  Show Profile  Reply with Quote
Thank you!
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.33 seconds. Powered By: Snitz Forums 2000