SET NOCOUNT ON select @Msg = '',@Err=0; select @BreakPoint=851810;
select @SDate=convert(char(8),@SDate,112); --如果已经统计过数据,为避免因日结失败的重复调用不再重新统计 select @count=count(*) from RPT_VenderLentCash where convert(char(8),SDate,112)=@SDate; if @Count >= 1 return 0; BEGIN TRAN;
Set @vPay_j = 'Pay_j'; Set @vSale_j = 'Sale_j';
--如果为月末,则按Sale_j、Pay_j修改表名后生成数据 if Day(DATEADD(day, 1, @SDate)) = 1 begin Set @vPay_j = 'Pay_j' + Convert(Char(6),Year(@SDate)*100+Month(@SDate)); Set @vSale_j = 'Sale_j' +Convert(Char(6),Year(@SDate)*100+Month(@SDate)); --如果表不存在,则退出 if not exists (select * from dbo.sysobjects where id = object_id(N'Pay_j' + Convert(Char(6),Year(@SDate)*100+Month(@SDate))) and OBJECTPROPERTY(id, N'IsUserTable') = 1) return 0; if not exists (select * from dbo.sysobjects where id = object_id(N'Sale_j' + Convert(Char(6),Year(@SDate)*100+Month(@SDate))) and OBJECTPROPERTY(id, N'IsUserTable') = 1) return 0; end;
select @BreakPoint=851820; Set @vSQL = N' declare Cur_SaleJ cursor for '+ N' select a.ShopID,a.PosID,a.ListNo,b.VenderID,sum(a.SaleValue-a.DiscValue) TrueValue '+ N' from '+@vSale_J+' a with (nolock),Cost b with (nolock) '+ N' where a.GoodsID=b.GoodsID and b.Flag=0 and a.SDate='''+convert(char(8),@SDate,112)+''' and a.TrainFlag=''0'' ' + N' group by a.ShopID,a.PosID,a.ListNo,b.VenderID'; exec sp_executesql @vSQL open Cur_SaleJ; select @Err = @@Error; if @Err != 0 goto ErrHandle;
select @BreakPoint=851830; while (1=1) BEGIN if @@Error != 0 goto ErrHandle; fetch next FROM Cur_SaleJ into @ShopID,@PosID,@ListNo,@VenderID,@TrueValue if @@fetch_status !=0 break;
Set @vSQL = N' select @PaySumValue =sum(EquivValue) from '+@vPay_j+N' with (nolock) ' + N' where ShopID='''+@ShopID+N''' and SDate= '''+convert(char(8),@SDate,112) + N''' and PosID='''+@PosID +N'''and ListNo='+convert(char(6),@ListNo)+N' and TrainFlag=''0'' '; exec sp_executesql @vSQL, N'@PaySumValue Decimal(12,5) output',@PaySumValue output if @PaySumValue is null or @PaySumValue=0 continue; Set @vSQL = N'select @SaleSumValue =sum(SaleValue-DiscValue) from '+@vSale_J+' with (nolock) '+ N'where ShopID='''+@ShopID + ''' and SDate='''+convert(char(8),@SDate,112)+''' and PosID='''+@PosID+''' and ListNo='+convert(char(6),@ListNo)+N' and TrainFlag=''0'' '; exec sp_executesql @vSQL, N'@SaleSumValue Decimal(12,5) output',@SaleSumValue output if @SaleSumValue is null or @SaleSumValue=0 continue;
select @BreakPoint=851840; Set @vSQL = N' declare Cur_PayJ cursor for ' + N'select STime,CashierID,PayReason,Paytype,CurrenCode,CardNo,PayValue,EquivValue '+ N'from '+@vPay_j+' with (nolock) '+ N'where ShopID='''+@ShopID+''' and SDate='''+convert(c har(8),@SDate,112)+''' and PosID='''+@PosID+''' and ListNo='+convert(char(6),@ListNo)+N' and TrainFlag=''0'''; exec sp_executesql @vSQL; open Cur_PayJ; select @Err = @@Error; if @Err != 0 goto ErrHandle; select @BreakPoint=851850; select @SeqID=1; while (1=1) BEGIN if @@Error != 0 goto ErrHandle; fetch next FROM Cur_PayJ into @STime,@CashierID,@PayReason,@Paytype,@CurrenCode, @CardNo,@PayValue,@EquivValue; if @@fetch_status !=0 break;
select @BreakPoint=851860; if @Paytype='R' or @PayType='r' Begin select @BankID=left(ltrim(@CardNo),1); select @BankName=Bank from CardNoteView where Bank_ID=@BankID end else begin select @BankID=null; select @BankName=null; end; select @Err = @@Error; if @Err != 0 goto ErrHandle;
select @CashierName=name from Clerk_LstView where clerk_id=@CashierID;
WITH ENCRYPTION
-----------------------------------------------------------------
--Version Number:MyShopV1R3_20041009_01,Last Mender:VL
-----------------------------------------------------------------
AS Begin
declare @Err int;
declare @BreakPoint int;
declare @Msg varchar(255);
declare @count int;
declare @ShopID char(4);
declare @PosID char(4);
declare @CashierID char(4);
declare @CashierName char(10);
declare @ListNo int;
declare @VenderID int;
declare @PayReason char(1);
declare @Paytype char(1);
declare @CurrenCode char(3);
declare @PaySumValue dec(12,3);
declare @SaleSumValue dec(12,3);
declare @PayValue dec(12,3);
declare @EquivValue dec(12,3);
declare @TrueValue dec(12,3);
declare @SaleValue dec(12,3);
declare @SeqID int;
declare @BankID char(1);
declare @BankName char(20);
declare @CardNo char(19);
declare @STime char(6);
declare @vSQL NVarChar(1000);
declare @vPay_j NVarChar(20);
declare @vSale_j NVarChar(20);
SET NOCOUNT ON
select @Msg = '',@Err=0;
select @BreakPoint=851810;
select @SDate=convert(char(8),@SDate,112); --如果已经统计过数据,为避免因日结失败的重复调用不再重新统计
select @count=count(*) from RPT_VenderLentCash where convert(char(8),SDate,112)=@SDate;
if @Count >= 1 return 0; BEGIN TRAN;
Set @vPay_j = 'Pay_j';
Set @vSale_j = 'Sale_j';
--如果为月末,则按Sale_j、Pay_j修改表名后生成数据
if Day(DATEADD(day, 1, @SDate)) = 1
begin
Set @vPay_j = 'Pay_j' + Convert(Char(6),Year(@SDate)*100+Month(@SDate));
Set @vSale_j = 'Sale_j' +Convert(Char(6),Year(@SDate)*100+Month(@SDate));
--如果表不存在,则退出
if not exists (select * from dbo.sysobjects
where id = object_id(N'Pay_j' + Convert(Char(6),Year(@SDate)*100+Month(@SDate)))
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
return 0;
if not exists (select * from dbo.sysobjects
where id = object_id(N'Sale_j' + Convert(Char(6),Year(@SDate)*100+Month(@SDate)))
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
return 0;
end;
select @BreakPoint=851820;
Set @vSQL =
N' declare Cur_SaleJ cursor for '+
N' select a.ShopID,a.PosID,a.ListNo,b.VenderID,sum(a.SaleValue-a.DiscValue) TrueValue '+
N' from '+@vSale_J+' a with (nolock),Cost b with (nolock) '+
N' where a.GoodsID=b.GoodsID and b.Flag=0 and a.SDate='''+convert(char(8),@SDate,112)+''' and a.TrainFlag=''0'' ' +
N' group by a.ShopID,a.PosID,a.ListNo,b.VenderID';
exec sp_executesql @vSQL open Cur_SaleJ;
select @Err = @@Error;
if @Err != 0 goto ErrHandle;
select @BreakPoint=851830;
while (1=1)
BEGIN
if @@Error != 0 goto ErrHandle;
fetch next FROM Cur_SaleJ into @ShopID,@PosID,@ListNo,@VenderID,@TrueValue
if @@fetch_status !=0 break;
Set @vSQL =
N' select @PaySumValue =sum(EquivValue) from '+@vPay_j+N' with (nolock) ' +
N' where ShopID='''+@ShopID+N''' and SDate= '''+convert(char(8),@SDate,112) + N''' and PosID='''+@PosID +N'''and ListNo='+convert(char(6),@ListNo)+N' and TrainFlag=''0'' ';
exec sp_executesql @vSQL, N'@PaySumValue Decimal(12,5) output',@PaySumValue output
if @PaySumValue is null or @PaySumValue=0
continue;
Set @vSQL =
N'select @SaleSumValue =sum(SaleValue-DiscValue) from '+@vSale_J+' with (nolock) '+
N'where ShopID='''+@ShopID + ''' and SDate='''+convert(char(8),@SDate,112)+''' and PosID='''+@PosID+''' and ListNo='+convert(char(6),@ListNo)+N' and TrainFlag=''0'' ';
exec sp_executesql @vSQL, N'@SaleSumValue Decimal(12,5) output',@SaleSumValue output
if @SaleSumValue is null or @SaleSumValue=0
continue;
select @BreakPoint=851840;
Set @vSQL =
N' declare Cur_PayJ cursor for ' +
N'select STime,CashierID,PayReason,Paytype,CurrenCode,CardNo,PayValue,EquivValue '+
N'from '+@vPay_j+' with (nolock) '+
N'where ShopID='''+@ShopID+''' and SDate='''+convert(c
har(8),@SDate,112)+''' and PosID='''+@PosID+''' and ListNo='+convert(char(6),@ListNo)+N' and TrainFlag=''0''';
exec sp_executesql @vSQL; open Cur_PayJ;
select @Err = @@Error;
if @Err != 0 goto ErrHandle; select @BreakPoint=851850;
select @SeqID=1;
while (1=1)
BEGIN
if @@Error != 0 goto ErrHandle;
fetch next FROM Cur_PayJ into @STime,@CashierID,@PayReason,@Paytype,@CurrenCode,
@CardNo,@PayValue,@EquivValue;
if @@fetch_status !=0 break;
select @BreakPoint=851860;
if @Paytype='R' or @PayType='r'
Begin
select @BankID=left(ltrim(@CardNo),1);
select @BankName=Bank from CardNoteView where Bank_ID=@BankID
end
else
begin
select @BankID=null;
select @BankName=null;
end;
select @Err = @@Error;
if @Err != 0 goto ErrHandle;
select @CashierName=name from Clerk_LstView where clerk_id=@CashierID;
select @PayValue=@PayValue*(@TrueValue*(@PaySumValue/@SaleSumValue)/@PaySumValue);
select @EquivValue=@EquivValue*(@TrueValue*(@PaySumValue/@SaleSumValue)/@PaySumValue);
if @SeqID = 1
select @SaleValue=@TrueValue;
else
select @SaleValue=0;
select @BreakPoint=851870;
insert into RPT_VenderLentCash(ShopID,SDate,STime,PosID,ListNo,CashierID,CashierName,VenderID,
PayReason,Paytype,CurrenCode,SaleValue,SeqID,PayValue,EquivValue,BankID,BankName,CardNo)
values(@ShopID,@SDate,@STime,@PosID,@ListNo,@CashierID,@CashierName,@VenderID,
@PayReason,@Paytype,@CurrenCode,@SaleValue,@SeqID,@PayValue,@EquivValue,@BankID,@BankName,@CardNo);
select @Err = @@Error;
if @Err != 0 goto ErrHandle;
select @SeqID=@SeqID+1;
end;
select @BreakPoint=851880;
close Cur_PayJ;
DEALLOCATE Cur_PayJ;
select @Err=@@Error;
if @Err != 0 or @Err is null
goto ErrHandle;
end;
select @BreakPoint=851890;
close Cur_SaleJ;
DEALLOCATE Cur_SaleJ;
select @Err=@@Error;
if @Err != 0 or @Err is null
goto ErrHandle; commit TRAN;
return 0; ErrHandle:
if @@TRANCOUNT > 0 rollback TRANsaction;
raisError('%s,断点=%d,Err=%d',16,1,@Msg,@BreakPoint,@Err);
return -1;End
输入BH取得数据集;
*/SELECT
Number,Name,Nation FROM Workers
WHERE
Number=@BH
Order by Number
GO
以上为一个普通的存储过程的写法.
as
delete from table1 where id=@i
运行存储过程
exec del
ADODataset1.CommandText:='Exec SP_TEST :BH';
ADODataSet1.Parameters.ParamValues['BH']:='2';
ADODataset1.Open;
query1.sql.clear;
query1.sql.add('exec 我的存储过程 '+我的参数);
query1.open;