将 varchar 值转换为数据类型 int 时，存储过程转换失败

本文关键字：转换 存储过程 失败 int 数据类型 varchar | 更新日期: 2023-09-27 18:33:31

在我的存储过程中，我遇到了以下问题 - 如何解决这个问题？

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'

我收到以下错误

Msg 245，级别 16，状态 1，过程 RupeshTest，第 4
行 将 varchar 值 '2,3,5,7,8' 转换为数据类型 int 时转换失败。

虽然我理解这个问题，但无法解决它，有人可以告诉我如何解决它。

将 varchar 值转换为数据类型 int 时，存储过程转换失败

这是行不通的，因为最终形成的查询是：

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')

你想要这样的东西：

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 子句。

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：

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

您在这里面临的问题是部分AnswerValue IN (2,3,5,7,8)以及如何使其有效 SQL，因为这些值是整数列表，并且将它们作为字符串。对我来说，一个技巧是将所有SQL命令变成一个字符串并执行它：

DECLARE @SQLQuery AS varchar(4000)
SET SQLQuery = 
'SELECT AnswerText '
+ ' FROM SurveyQuestionAnswerTypes '
+ ' WHERE AnswerType = (SELECT AnswerType '
                   + ' FROM SurveyQuestions '
                   + ' WHERE QuestionID = ' + CAST(@QueId AS VARCHAR(4))) 
                    + ' AND AnswerValue IN (' + (REPLACE(@Answer,'^',',')) + ')'

EXECUTE(@SQLQuery)

顺便说一句，没有理由发送带有 ^ 的数字然后更改它。

您可以将

最后一个条件替换为

AND CHARINDEX(CONCAT("^", CAST(AnswerValue AS CHAR), "^"), CONCAT("^", @Answer, "^"))

这似乎并不完全正确，但它可能会解决问题。

编辑

感谢您的更正。

相关文章：