error message in CURSOR -conversion failed from varchar to int

Question

0.00/5 (No votes)

See more:

Hi friends,
I am getting the error message as 'Conversion failed when converting the varchar value '61,9' to data type int.'

Please help me

Thanks.

SQL

DECLARE @FieldTypeID INT
DECLARE @ColumnName INT
DECLARE @SQL VARCHAR(MAX)
DECLARE @Error VARCHAR(MAX)
DECLARE @array_value VARCHAR(100)='contracts'
DECLARE @tempDatasetID VARCHAR(100)='284'
DECLARE @fieldID VARCHAR(100)='61,9'

DECLARE cColumns CURSOR FOR

  SELECT c.ColumnName,q.QuestionTypeID FROM tblColumns c
  INNER JOIN tblQuestions q ON (c.QuestionnaireID=q.QuestionnaireID AND c.QuestionID=q.QuestionID)
  WHERE c.QuestionnaireID=284 AND c.QuestionID IN (@fieldID)

  OPEN cColumns
  FETCH NEXT FROM cColumns INTO @ColumnName, @FieldTypeID
  WHILE @@FETCH_STATUS = 0
  BEGIN
        IF @FieldTypeID=1 OR @FieldTypeID=7
          BEGIN
           SELECT @SQL=@SQL+'['+CAST(@ColumnName AS VARCHAR)+'] in (SELECT OptionID FROM tblQuestionOptions tqo WHERE tqo.QuestionnaireID='+CAST(@tempDatasetID AS Varchar)+' AND tqo.QuestionID='+CAST(@ColumnName AS VARCHAR)+' AND tqo.[Description] LIKE ''%'+@array_value+'%'')  OR '
          END
        ELSE
         BEGIN
          SELECT @SQL=@SQL+'['+CAST(@ColumnName AS VARCHAR)+'] like ''%'+@array_value+'%'' OR '
         END

  SELECT @Error = @@ERROR
  --PRINT (@SQL_Insert+@SQL_Values+@SQL)

   FETCH NEXT FROM cColumns INTO @ColumnName, @FieldTypeID

  END
  CLOSE cColumns
  DEALLOCATE cColumns

Posted 9-Feb-12 20:18pm

Pravinjas

Updated 9-Feb-12 20:19pm

v2

Add a Solution

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CRDave1988 · Answer 1 · 2012-02-09T20:30:00

Solution 1

c.QuestionID is number so it try to conver '16,9' to int which is invalid due to char ','

store 16 and 9 in different variable and write

c.QuestionID IN (@fieldID1,@fieldID2)

and no need to declare them as varchar

Posted 9-Feb-12 20:30pm

CRDave1988

Comments

Pravinjas 10-Feb-12 2:42am

16,9 is a dynamic string means there may be multiple number so how I store them in different variable
sometime there will be 16,9,2,5,7,3,0

CRDave1988 10-Feb-12 2:55am

I know that what u think but problem is that SQl-server is not thinking like us. try this ur problem may solved http://www.codeproject.com/Articles/30681/Split-parameter-string-from-comma-seperator-in-SQL

Pravinjas 10-Feb-12 3:52am

Thanks... Now it is working.

Om Prakash Pant · Answer 2 · 2012-02-09T20:35:00

Field ID (@fieldID) is varchar with values 61, 9 which you are using in IN, which will not work

SQL

SELECT c.ColumnName,q.QuestionTypeID FROM tblColumns c
INNER JOIN tblQuestions q ON (c.QuestionnaireID=q.QuestionnaireID AND c.QuestionID=q.QuestionID)
WHERE c.QuestionnaireID=284 AND c.QuestionID IN (@fieldID)

One option is to declare 2 variables and use them:

SQL

DECLARE @fieldID1 INT = 61
DECLARE @fieldID2 INT = 9

  SELECT c.ColumnName,q.QuestionTypeID FROM tblColumns c
  INNER JOIN tblQuestions q ON (c.QuestionnaireID=q.QuestionnaireID AND c.QuestionID=q.QuestionID)
  WHERE c.QuestionnaireID=284 AND c.QuestionID IN (@fieldID1, @fieldID2)

Other option is to use a split function and convert the array into temp table and use the same.

CRDave1988 · Answer 3 · 2012-02-09T20:55:00

Solution 4

try this:

Split parameter string from comma seperator in SQL IN clause[^]

Posted 9-Feb-12 20:55pm

CRDave1988

Comments

Pravinjas 10-Feb-12 3:51am

Thanks a lot. Its working. my 100+

Sergey Alexandrovich Kryukov · Answer 4 · 2012-02-09T20:36:00

Solution 3

Depending on the application, you should use either the exact numeric type decimal or numeric or approximate numeric type float or double, instead of varchar(100) in this case.

Please see: http://msdn.microsoft.com/en-us/library/aa258271%28v=sql.80%29.aspx[^].

—SA

Posted 9-Feb-12 20:36pm

Sergey Alexandrovich Kryukov

v2