为什么要忽略存储过程中的while循环

I have a stored procedure that starts with the following as my while loop:

我有一个存储过程,以我的while循环开头:

WHILE(SELECT COUNT(*)
          FROM LinkDB
          WHERE BaseValue = @Guid
          AND 'PENDING WORKFLOW' = (SELECT TOP(1) Status 
                                FROM BaseDB
                                WHERE UniqueIdentifier IN (SELECT LinkValue
                                               FROM LinkDB
                                               WHERE BaseValue = @Guid))) > 0

I have a C# program that executes the above, but if I look in the DB it appears to ignore the while loop at times. What could be causing this? If I run the select seperately it returns a value above 0

我有一个C#程序执行上面的操作,但如果我查看数据库,它似乎有时会忽略while循环。可能是什么导致了这个?如果我单独运行select它会返回一个大于0的值

1 个解决方案

#1

This is non deterministic
You are not guaranteed to got the same top (1) from run to run

这是不确定的。您不能保证从运行到运行时获得相同的顶部(1)

SELECT TOP(1) Status 
  FROM BaseDB
 WHERE UniqueIdentifier IN (SELECT LinkValue
                              FROM LinkDB
                             WHERE BaseValue = @Guid)

that could be

那可能是

 SELECT TOP(1) Status 
   FROM BaseDB
   JOIN LinkDB 
     ON LinkDB.LinkValue = BaseDB.UniqueIdentifier 
    and LinkDB.BaseValue = @Guid 
--where needed deterministic where

-- you sure the whole thing is not this ?

- 你确定整件事不是这个吗?

 SELECT count(*)
   FROM BaseDB
   JOIN LinkDB 
     ON LinkDB.LinkValue = BaseDB.UniqueIdentifier 
    and LinkDB.BaseValue = @Guid 
    and BaseDB.Status = 'PENDING WORKFLOW'

#1