一个简单的SQL游标示例的存储过程

USE [Princess]
GO
/****** Object:  StoredProcedure [dbo].[p_PaymentTransactions_Insert]    Script Date: 08/10/2012 10:33:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[p_PaymentTransactions_Insert]
    @PaymentID integer, @InvoiceNo varchar(10), @BalanceAmount decimal(14,2),@MatchBy int
AS
    declare @InvoiceIDs varchar(200)
    declare @Balance money
    declare @No varchar(50)

    BEGIN TRANSACTION  --事务开始
    insert into Payment_Transactions (PaymentID, InvoiceNo,BalanceDate,BalanceAmount,MatchBy,MatchDate)
    values(@PaymentID, @InvoiceNo,getdate(),@BalanceAmount,@MatchBy,getdate())

    select @Balance = Balance from Payments where PaymentID = @PaymentID
    set @Balance = @Balance - @BalanceAmount
    
    --游标读取InvoiceIDs
    declare rs cursor for select InvoiceNo from Payment_Transactions where PaymentID = @PaymentID
    open rs
    fetch next from rs into @No
    while @@FETCH_STATUS=0
    BEGIN
        IF @InvoiceIDs != ''
        BEGIN
            IF CHARINDEX(@No,@InvoiceIDs)<=0 --函数CHARINDEX()判断@InvoiceIDs变量中是否包含@No变量里面的内容
            BEGIN
                set @InvoiceIDs = isnull(@InvoiceIDs, '') + @No + ','
            END
        END
        ELSE
        BEGIN
            set @InvoiceIDs = @No + ','
        END
        
        fetch next from rs into @No
    end
    close rs
    deallocate rs
    --游标结束
    
    SET @InvoiceIDs = SUBSTRING(@InvoiceIDs,1,LEN(@InvoiceIDs)-1) --去除@InvoiceIDs变量中末尾"，"
    update Payments set Balance =  @Balance, InvoiceIDs = @InvoiceIDs where PaymentID = @PaymentID
    
    exec p_UpdateInvoice_Balance @InvoiceNo  --调用另外一个存储过程
    
    COMMIT TRANSACTION  --提交事务