USE [Princess]GO/****** Object:  StoredProcedure [dbo].[p_PaymentTransactions_Insert]    Script Date: 08/10/2012 10:33:12 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[p_PaymentTransactions_Insert]    @PaymentID integer, @InvoiceNo varchar(10), @BalanceAmount decimal(14,2),@MatchBy intAS    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  --提交事务