SQL Server将varchar字符串转换为int

Question

嗨，我有以下存储过程

SELECT DISTINCT QuestionId, AnswerId, COUNT(AnswerId) AS Cntr,
    (SELECT     COUNT(AnswerId) AS ttl
     FROM          QUserAnswers
     WHERE      (QuestionId = QUAM.QuestionId)) AS TtlCnt
FROM         QUserAnswers AS QUAM
WHERE     (QuestionId IN (@QuestionIdIn))
GROUP BY QuestionId, AnswerId
ORDER BY QuestionId

然而，我在格式为"1,2,3,4,5'"的@QuestionIdI中传递的，它在将varchar值'1,2,3,4,5,6'转换为数据类型int时引发错误Conversion失败。

有人能给我一些建议吗？

Answer 1

Tim Schelter在他提供的链接中建议：-

首先，您需要创建一个函数来解析输入

 CREATE FUNCTION inputParser (@list nvarchar(MAX))
 RETURNS @tbl TABLE (number int NOT NULL) AS
 BEGIN
 DECLARE @pos        int,
       @nextpos    int,
       @valuelen   int
 SELECT @pos = 0, @nextpos = 1
 WHILE @nextpos > 0
 BEGIN
  SELECT @nextpos = charindex(',', @list, @pos + 1)
  SELECT @valuelen = CASE WHEN @nextpos > 0
                          THEN @nextpos
                          ELSE len(@list) + 1
                     END - @pos - 1
  INSERT @tbl (number)
     VALUES (convert(int, substring(@list, @pos + 1, @valuelen)))
  SELECT @pos = @nextpos
 END
 RETURN
END

然后在您的SP 中使用该功能

 CREATE PROCEDURE usp_getQuestion
 @QuestionIdIn varchar(50) 
 AS
 Begin
 Select Distinct QuestionId, AnswerId, COUNT(AnswerId) AS Cntr,
    (SELECT     COUNT(AnswerId) AS ttl
      FROM   QUserAnswers
      WHERE      QuestionId = QUAM.QuestionId) as TtlCnt
 from QUserAnswers AS QUAM
 inner join inputParser (@QuestionIdIn) i ON QuaM.QuestionId = i.number
 GROUP BY QuestionId, AnswerId
 ORDER BY QuestionId
 End
EXEC usp_getQuestion '1, 2, 3, 4'

Answer 2

而不是

WHERE     (QuestionId IN (@QuestionIdIn)) 

使用

WHERE charindex(','+cast(@QuestionId as varchar(100))+',',','+@QuestionID+',')>0

如果您担心性能，请使用split函数并与主表连接

Answer 3

尝试在那里应用此逻辑。

Declare @str varchar(100)='''1'',''2'',''3'',''4'',''5'''
select @str
DECLARE @s int = 1
select * from emp where CAST(@s as varchar) in ('1','2','3','4','5')

Answer 4

尝试更改条件：

WHERE     (QuestionId IN (@QuestionIdIn))

至

where (','+@QuestionIdIn+',' like '%,'+convert(varchar(50),QuestionId)+',%')

例如，对于@QuestionIdIn='1,2,3,4,5,6'和QuestionId=3，它的意思是：

where (',1,2,3,4,5,6,' like '%,3,%')

如果3在这个列表中，那是真的。