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 --提交事务