给自己公司开发的管理系统,数据库是SQL Server 2000, 已经用了5年了,一直没发现有什么问题。今天采购部向我报告有一张采购单据无法入库,我看了一下确实如此。通过跟踪程序,发现问题出在我写的一个存储过程上,进一步跟踪执行存储过程,发现在执行这一条语句时出了问题:
(当然这不是在存储过程中的语句,而是我跟踪程序后用实际执行的数据在查询分析器里执行的)
select patindex('%22523%','22516,22517,22518,22519,22520,22521,22522,22523,22524,')
这个语句的本意是检索22523这个关键字在后面字符串里的索引位置,正确结果应该是43,而这里返回的结果是42
我测试其他的数据都能正确返回索引位置,比如:
select patindex('%22516%','22516,22517,22518,22519,22520,22521,22522,22523,22524,') = 1
select patindex('%22517%','22516,22517,22518,22519,22520,22521,22522,22523,22524,') = 7
select patindex('%22518%','22516,22517,22518,22519,22520,22521,22522,22523,22524,') = 13
select patindex('%22519%','22516,22517,22518,22519,22520,22521,22522,22523,22524,') = 19
select patindex('%22520%','22516,22517,22518,22519,22520,22521,22522,22523,22524,') = 25
select patindex('%22521%','22516,22517,22518,22519,22520,22521,22522,22523,22524,') = 31
select patindex('%22522%','22516,22517,22518,22519,22520,22521,22522,22523,22524,') = 37
select patindex('%22523%','22516,22517,22518,22519,22520,22521,22522,22523,22524,') = 42 (这个错了,应该是43)
select patindex('%22524%','22516,22517,22518,22519,22520,22521,22522,22523,22524,') = 49
除了22523返回错误外其他结果都是正确的.到底出了什么问题?
(当然这不是在存储过程中的语句,而是我跟踪程序后用实际执行的数据在查询分析器里执行的)
select patindex('%22523%','22516,22517,22518,22519,22520,22521,22522,22523,22524,')
这个语句的本意是检索22523这个关键字在后面字符串里的索引位置,正确结果应该是43,而这里返回的结果是42
我测试其他的数据都能正确返回索引位置,比如:
select patindex('%22516%','22516,22517,22518,22519,22520,22521,22522,22523,22524,') = 1
select patindex('%22517%','22516,22517,22518,22519,22520,22521,22522,22523,22524,') = 7
select patindex('%22518%','22516,22517,22518,22519,22520,22521,22522,22523,22524,') = 13
select patindex('%22519%','22516,22517,22518,22519,22520,22521,22522,22523,22524,') = 19
select patindex('%22520%','22516,22517,22518,22519,22520,22521,22522,22523,22524,') = 25
select patindex('%22521%','22516,22517,22518,22519,22520,22521,22522,22523,22524,') = 31
select patindex('%22522%','22516,22517,22518,22519,22520,22521,22522,22523,22524,') = 37
select patindex('%22523%','22516,22517,22518,22519,22520,22521,22522,22523,22524,') = 42 (这个错了,应该是43)
select patindex('%22524%','22516,22517,22518,22519,22520,22521,22522,22523,22524,') = 49
除了22523返回错误外其他结果都是正确的.到底出了什么问题?
-----------
43(所影响的行数为 1 行)
*/经过测试,没问题.
49(1 行受影响)me too
select patindex('%22523%','22516,22517,22518,22519,22520,22521,22522,22523,22524,') = 42 中的'%22523%'有可能就是出现两次42 43位置上都是的。。你自己看一下就行了啊,到底是第几个
--43没报错哦!
43(1 行受影响)
select patindex('%22523%','22516,22517,22518,22519,22520,22521,22522,22523,22524,')(1 行受影响)43是不是少了一个逗号
莫非是我的SQL软件版本有问题?
我返回的也是42
这个
跟版本之类的有没有关系啊
我用的个人版的SQL 2000
---------------------------------
CREATE PROCEDURE [OrderBillInStock]
@OrderID varchar(50),@PrepareIDList varchar(800),@PrepareCountList varchar(800),
@Supply varchar(200),@LinkMan varchar(50),@AcceptMan varchar(50)
AS
DECLARE @AutoID varchar(50),@OrderCount int,@InCount int,@iPos int,@iCount int,@InBillAdded bit,
@InBillID varchar(50),@MatID varchar(50),@UnitPrice numeric(18,2),@RetMessage varchar(800)
--判别该订货单是否已完成
IF dbo.Check_OrderFinish(@OrderID)=0 --未完成
BEGIN
SELECT @InBillAdded=0 --将入库单添加标志置为0
--定义待处理数据集的游标
DECLARE cur_order CURSOR FOR
SELECT 编号,数量,入库数量,物料编码,单价 FROM 订货单未入库物料视图 WHERE 订货单号=@OrderID OPEN cur_order
FETCH NEXT FROM cur_order INTO @AutoID,@OrderCount,@InCount,@MatID,@UnitPrice
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @iPos=PATINDEX('%'+@AutoID+',%',@PrepareIDList) --iPos=当前编号在ID表中的位置
IF @iPos<>0
BEGIN
SELECT @iCount=CAST(SUBSTRING(@PrepareCountList,@iPos,5)AS INT) --iCount=对应的数量
--SELECT @iCount AS 准备入库量,@OrderCount AS 订货量,@InCount AS 已入库数量
IF @iCount<=@OrderCount-@InCount
BEGIN
IF @InBillAdded=0
BEGIN
--这里添加新的入库单
EXEC GetBillNo '入库单'
SELECT @InBillID=编码+'-'+CAST(序号 AS VARCHAR) FROM 单据分类表 WHERE 类别='入库单'
INSERT INTO 入库单表(入库单号,入库类型,入库日期,供应商,联系人,验收人,订货单号)
VALUES(@InBillID,'订货入库',GetDate(),@Supply,@LinkMan,@AcceptMan,@OrderID) SELECT @InBillAdded=1
END
--这里添加入库单明细表
INSERT INTO 入库单明细表(入库单号,物料编码,数量,单价)
VALUES(@InBillID,@MatID,@iCount,@UnitPrice)
END
END
FETCH NEXT FROM cur_order INTO @AutoID,@OrderCount,@InCount,@MatID,@UnitPrice
END
IF @InBillAdded=1
BEGIN
SELECT @RetMessage='已成功添加入库单:'+@InBillID
END
ELSE
BEGIN
SELECT @RetMessage='未能正确添加入库单,可能由于:1.到货数量超过实际可能的最大入库量;2.并发操作其他用户已完成入库。'
END --清除游标
CLOSE cur_order
DEALLOCATE cur_order
END
ELSE
BEGIN
SELECT @RetMessage='未能正确添加入库单,该订货单被其他用户完成入库工作!'
END --返回执行消息
SELECT @RetMessage AS Message
GO
---------------------------------------------------
同志啊,可算找到一个和我执行结果相同的了,On earth怎么回事呀?
sp4中测试是43
select charindex('22523','22516,22517,22518,22519,22520,22521,22522,22523,22524,') = 42
一样的,估计是MS算法有Bug。
等我更新到SP4再试下看看是不是修改了。
select patindex('%22522%','22516,22517,22518,22519,22520,22521,22522,22523,22524,')
返回值为43.
chuifengde 是对的,感谢chuifengde的帮助。