SQL游标处理控制中断逻辑

Question

我们仍然是这样处理sql游标中的控制中断逻辑的吗

基本上，我想做的是循环遍历帐户列表，如果下一个帐户名称与前一个帐户名称相同，则执行许多其他操作。

下面的示例仅列出了所有帐户以及它们在列表中出现的次数。理想情况下，我将在每种情况下执行更多处理(比如调用多个存储过程)。还有别的办法吗?

DECLARE @CompanyName NVARCHAR(255)
DECLARE @CompanyNameHold NVARCHAR(255)
DECLARE @TESTCOUNT INT
DECLARE @TOTALCOUNT INT
SELECT @CompanyNameHold = ''
SELECT @TESTCOUNT = 0
SELECT @TOTALCOUNT = 0
DECLARE ImportCursor CURSOR FOR
SELECT [Company Name]
  FROM [MyDB].[dbo].[AccountsToImport]
  ORDER BY [Company Name]

OPEN ImportCursor
FETCH NEXT FROM ImportCursor INTO @CompanyName
WHILE @@FETCH_STATUS = 0
BEGIN

    --Check HoldCompanyName = CompanyName   
    IF (@CompanyName <> @CompanyNameHold)   
    BEGIN   
        --PROCESS NEW ACCOUNT
        IF @TESTCOUNT = 0 --PRIMER
            SET @CompanyNameHold = @CompanyName
        ELSE        
            PRINT CAST(@TESTCOUNT AS NVARCHAR) + ' - ' + @CompanyNameHold 
        SET @TESTCOUNT = 1
    END
    ELSE
    BEGIN
        --ADD TO EXISTING
        SET @TESTCOUNT = @TESTCOUNT + 1
    END

    --Move CompanyName to HoldCompanyName
    SET @CompanyNameHold = @CompanyName
    SET @TOTALCOUNT = @TOTALCOUNT + 1
    FETCH NEXT FROM ImportCursor INTO @CompanyName
END
    --Process Last Record 
    PRINT CAST(@TESTCOUNT AS NVARCHAR) + ' - ' + @CompanyNameHold 
CLOSE ImportCursor
DEALLOCATE ImportCursor

Answer 1

可以在不使用游标的情况下查询每个公司的行数:

select  [Company Name]
,       count(*) as RowsPerCompany
from    MyDB.dbo.AccountsToImport
group by
        [Company Name]

但是要为每一行调用存储过程，就需要一个游标。在遍历游标时，可以调用任意数量的存储过程。