Question

sql存储过程数组查询 报错

查询条件，用存储过程查询下面语句

select paperid,Charge from ChargeOfPaper where paperid=1 or paperid=2 or paperid=5
paperid为int类型，Charge 为double类型

实现方式是
exec proc_selectPaperIdList '1‘，’2’，‘5’

语句是
alter PROCEDURE proc_selectPaperIdList
@ID nvarchar(500)
as
DECLARE @PointerPrev int
DECLARE @PointerCurr int
DECLARE @sqlcode varchar(500)
DECLARE @TId varchar(100)
Set @PointerPrev=1

while (@PointerPrev < LEN(@ID))
Begin
Set @PointerCurr=CharIndex(',',@ID,@PointerPrev)
if(@PointerCurr>0)
Begin
set @TId=SUBSTRING(@ID,@PointerPrev,@PointerCurr-@PointerPrev)
SET @PointerPrev = @PointerCurr+1
set @sqlcode=@sqlcode+' paperid= '+@TId+' or '
End
else
Break
End
--删除最后一个,因为最后一个后面没有逗号,所以在循环中跳出,需另外再删除
set @sqlcode=@sqlcode+' paperid= '+@TId
EXEC('select paperid,Charge from ChargeOfPaper where '+ @sqlcode)
GO

报错求解
'where' 附近有语法错误。

Answer 1

LEN(@ID) 为null