Error converting varchar value

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)

Error converting varchar value

Author

Topic

CreativeNRG
Starting Member

44 Posts

Posted - 2006-04-15 : 06:55:08

The following dynamic SQL statement executes with the desired results for a question that has a simple yes/no answer.

SELECT dbo.tblQuestionnaireNP.[id], FirstName + ' ' + LastName AS Name, CompanyName, CASE
WHEN Q2a = 0 THEN 'No'
WHEN Q2a= 1 THEN 'Yes'
ELSE CAST(Q2a AS VARCHAR)
END
AS Q2a
FROM dbo.tblQuestionnaireNP

This same piece of SQL is also used to retrive aswers to questions that are not always simple yes/no answers and when I execute the sql on these questions I get the following error and now I am stuck.

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'Good ' to a column of data type int.

Any help would be most appreciated.

www.creativenrg.co.uk

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-15 : 07:04:59

what is the datatype of the columns Q2a??

and just check there must be some data varchar data..

or try this

SELECT dbo.tblQuestionnaireNP.[id], FirstName + ' ' + LastName AS Name, CompanyName, CASE
WHEN Cast(Q2a As Varchar) = '0' THEN 'No'
WHEN Cast(Q2a As Varchar) = '1' THEN 'Yes'
ELSE CAST(Q2a AS VARCHAR)
END
AS Q2a
FROM dbo.tblQuestionnaireNP

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

CreativeNRG
Starting Member

44 Posts

Posted - 2006-04-15 : 07:24:28

The column data type can vary depending on the question and the query is generated dynamically so I need a generic query that will work regardless of the data type. It falls over when the question column is something other than a bit field.

The solution you offered still does not overcome the error unforunately.

www.creativenrg.co.uk

CreativeNRG
Starting Member

44 Posts

Posted - 2006-04-15 : 07:26:44

quote:
Originally posted by CreativeNRG

The column data type can vary depending on the question and the query is generated dynamically so I need a generic query that will work regardless of the data type. It falls over when the question column is something other than a bit field.

The solution you offered still does not overcome the error unforunately.

www.creativenrg.co.uk

Sorry my fault, your query works perfectly I simply missed the quotes around '0' and '1'.

Thank you for the help.

www.creativenrg.co.uk

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-15 : 07:53:30

Can you post some sample data ?? waht can be in question column???

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources