转换VARCHAR值数据类型为int时，存储过程转换失败？ [英] Stored procedure conversion failed when converting the varchar value to data type int?

问题描述

在我的存储过程中，我面临着以下问题 - 我如何才能解决这个问题。

In my stored procedure, I am facing the following problem - how can i fix this?

CREATE PROC RupeshTest(@QueID int, @Answer varchar(250)) as
BEGIN
   DECLARE @STR varchar(4000) 

   SELECT @STR = COALESCE(@str + ';','') + AnswerText     
   FROM SurveyQuestionAnswerTypes 
   WHERE AnswerType = (SELECT AnswerType 
                       FROM SurveyQuestions 
                       WHERE QuestionID = CAST(@QueId AS VARCHAR(4))) 
                         AND AnswerValue IN (REPLACE(@Answer,'^',','))
END

当我运行它

RupeshTest 25, '2^3^5^7^8' 

我得到以下错误

消息245，级别16，状态1，过程RupeshTest，4号线结果
  转换为varchar值'-1,2,3,5,7,8-'为数据类型int时转换失败。

Msg 245, Level 16, State 1, Procedure RupeshTest, Line 4
Conversion failed when converting the varchar value '2,3,5,7,8' to data type int.

虽然我理解的问题，但无法修复它，可一些身体告诉我，我该怎么解决这个问题。

Although I understand the problem, but unable to fix it, could some body tell me how can I fix it.

推荐答案

这不会工作，因为形成最终的查询是：

This wont work, since the final Query being formed is :

SELECT @STR = COALESCE(@str + ';','') + AnswerText     
   FROM SurveyQuestionAnswerTypes 
   WHERE AnswerType = (SELECT AnswerType 
                       FROM SurveyQuestions 
                       WHERE QuestionID = CAST(@QueId AS VARCHAR(4))) 
                         AND AnswerValue IN ('2,3,5,7,8')

在哪里，只要你想是这样的：

Where as you want something like this:

SELECT @STR = COALESCE(@str + ';','') + AnswerText     
   FROM SurveyQuestionAnswerTypes 
   WHERE AnswerType = (SELECT AnswerType 
                       FROM SurveyQuestions 
                       WHERE QuestionID = CAST(@QueId AS VARCHAR(4))) 
                         AND AnswerValue IN (2,3,5,7,8)

我会建议你做一个UDF String_To_Table，字符串传递给UDF和使用IN子句。

I would advice you to make a UDF String_To_Table, pass the string to that UDF and use for the IN clause.

String_To_Table UDF：

String_To_Table UDF:

CREATE FUNCTION [dbo].[String_To_Table]
                    (@string      VARCHAR(4000),
                     @delimiter CHAR(1) = ';')
         RETURNS @tbl TABLE (ord INT IDENTITY(1, 1) NOT NULL,
                             token     VARCHAR(500)) AS

   BEGIN
      DECLARE @pos      int,
              @textpos  int,
              @chunklen smallint,
              @tmpstr   varchar(4000),
              @leftover varchar(4000),
              @tmpval   varchar(4000)

      SET @textpos = 1
      SET @leftover = ''
      WHILE @textpos <= datalength(@string)
      BEGIN
         SET @chunklen = 4000 - datalength(@leftover)
         SET @tmpstr = @leftover + substring(@string, @textpos, @chunklen)
         SET @textpos = @textpos + @chunklen

         SET @pos = charindex(@delimiter, @tmpstr)

         WHILE @pos > 0
         BEGIN
            SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1)))
            INSERT @tbl (token) VALUES(@tmpval)
            SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr))
            SET @pos = charindex(@delimiter, @tmpstr)
         END

         SET @leftover = @tmpstr
      END

    IF ltrim(rtrim(@leftover)) <> ''
        INSERT @tbl(token) VALUES (ltrim(rtrim(@leftover)))


 RETURN
   END

然后你就可以改变你的SP，如下：

Then you can change your SP as below:

CREATE PROC RupeshTest(@QueID int, @Answer varchar(250)) as
BEGIN
   DECLARE @STR varchar(4000) 

   SELECT @STR = COALESCE(@str + ';','') + AnswerText     
   FROM SurveyQuestionAnswerTypes 
   WHERE AnswerType = (SELECT AnswerType 
                       FROM SurveyQuestions 
                       WHERE QuestionID = CAST(@QueId AS VARCHAR(4))) 
                         AND AnswerValue IN (SELECT Token FROM dbo.String_To_Table(@Answer,'^'))
END

这篇关于转换VARCHAR值数据类型为int时，存储过程转换失败？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！