CURSOR中的错误消息-从varchar到int的转换失败 [英] error message in CURSOR -conversion failed from varchar to int
本文介绍了CURSOR中的错误消息-从varchar到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屋!
查看全文