CURSOR中的错误消息-从varchar到int的转换失败 [英] error message in CURSOR -conversion failed from varchar to int

问题描述

朋友们，
我收到错误消息，因为``将varchar值``61,9''转换为数据类型int时转换失败.''

请帮助我

谢谢.

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.

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

推荐答案

c.QuestionID是数字，因此它尝试转换为"16" ，9''到int，由于char''，''
而无效
将16和9存储在不同的变量中并写入

c.QuestionID IN(@ fieldID1，@ fieldID2)

并且无需将它们声明为varchar

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

字段ID(@fieldID)是varchar，其值61、9在IN中使用，将不起作用

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

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)

一种选择是声明2个变量并使用它们:

One option is to declare 2 variables and use them:

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.

请尝试以下操作:

在SQL IN子句中从逗号分隔符分割参数字符串 [^ ]

try this:

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

这篇关于CURSOR中的错误消息-从varchar到int的转换失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！