sql.dll

仓库管理系统的部分代码！！！！设计时可以参考

    fetch tCur_Table into @T_No, @Make_Date, @Summary, @In_Number
  end
  close tCur_Table
  deallocate tCur_Table
  select @ExecuteString = 'drop table ' + @ttTableName
  exec (@ExecuteString)

  fetch Cur_Table into @tTableName
end
close Cur_Table
deallocate Cur_Table
go

------------------------------------------------------------
--取得供应商的金额帐(根据：起止日期、供应商编号)结果存放在@TableName表中
create procedure Get_VendorMoneyBill 
  (@StartDate datetime, 
  @EndDate datetime, 
  @V_No char(6), 
  @TableName char(20)) WITH ENCRYPTION as
declare @ExecuteString nvarchar(2000)
declare @Amount money
declare @T_No char(12), @Make_Date datetime, @Summary char(10), @In_Amount money
--select @ExecuteString = 'drop table ' + RTrim(@TableName)
--exec (@ExecuteString)
select @ExecuteString = 'create table ' 
  + RTrim(@TableName) 
  + ' (R_No int NOT NULL IDENTITY PRIMARY KEY CLUSTERED,'
  + 'T_No char(12),'
  + 'Make_Date datetime,'
  + 'Summary char(10),'
  + 'In_Amount Money,'
  + 'Out_Amount Money,'
  + 'Rest_Amount Money)'
exec (@ExecuteString)
select @Amount = sum(In_Amount) 
  from VendorMoneyBill 
  where V_No = @V_No and Make_Date < @StartDate
if isnull(@Amount, 0) <> 0
begin
  select @ExecuteString = 'insert ' 
    + RTrim(@TableName) 
    + ' (Make_Date, Summary, Rest_Amount) values (''' 
    + convert(char(10), @StartDate, 102) 
    + ''',''期前结余'',' 
    + ltrim(str(@Amount)) 
    + ')'
  exec (@ExecuteString)
end
else
begin
 select @Amount = 0
end
set nocount on
declare Cur_Table Cursor for 
  select T_No, Make_Date, Summary, In_Amount 
  from VendorMoneyBill 
  where Make_Date >= @StartDate and Make_Date <= @EndDate and V_No = @V_No 
  order by Make_Date for read only
open Cur_Table
fetch Cur_Table into @T_No, @Make_Date, @Summary, @In_amount
while @@fetch_status = 0
begin
  select @Amount = @Amount + @In_Amount
  if @In_Amount >= 0
  begin
    select @ExecuteString = 'insert ' 
      + RTrim(@TableName) 
      + ' (T_No, Make_Date, Summary, In_Amount, Rest_Amount) values (''' 
      + @T_No 
      + ''',''' 
      + convert(char(10), @Make_Date, 102) 
      + ''',''' 
      + RTrim(@Summary) 
      + ''',' 
      + ltrim(str(@In_amount)) 
      + ',' 
      + ltrim(str(@Amount)) 
      + ')'
  end
  else
    select @ExecuteString = 'insert ' 
      + RTrim(@TableName) 
      + ' (T_No, Make_Date, Summary, Out_Amount, Rest_Amount) values (''' 
      + @T_No 
      + ''',''' 
      + convert(char(10), @Make_Date, 102) 
      + ''',''' 
      + RTrim(@Summary) 
      + ''',' 
      + ltrim(str(-@In_amount)) 
      + ',' 
      + ltrim(str(@Amount)) 
      + ')'
  exec (@ExecuteString)
  fetch Cur_Table into @T_No, @Make_Date, @Summary, @In_amount
end
close Cur_Table
deallocate Cur_Table
go
------------------------------------------------------------
--取得供应商的商品帐(根据：起止日期、供应商编号、商品编号)结果存放在@TableName表中
create procedure Get_VendorGoodsBill 
  (@StartDate datetime, 
  @EndDate datetime, 
  @V_No char(6), 
  @G_No char(13), 
  @TableName char(20)) WITH ENCRYPTION as
declare @ExecuteString nvarchar(2000)
declare @tTableName char(128), @startTableName char(128), @endTableName char(128)
declare @Number Float, @tNumber Float
declare @T_No char(12), @Make_Date datetime, @Summary char(10), @In_Number float
declare @ttTableName char(128)

select @ExecuteString = 'create table ' 
  + RTrim(@TableName) 
  + ' (R_No int NOT NULL IDENTITY PRIMARY KEY CLUSTERED,'
  + 'T_No char(12),'
  + 'Make_Date datetime,'
  + 'Summary char(10),'
  + 'In_Number Float,'
  + 'Out_Number Float,'
  + 'Rest_Number Float)'
exec (@ExecuteString)
select @startTableName = convert(char(10), @StartDate, 102)
select @startTableName = 'V' + subString(@startTableName,1,4) + subString(@startTableName,6,2)
select @endTableName = convert(char(10), @EndDate, 102)
select @endTableName = 'V' + subString(@endTableName,1,4) + subString(@endTableName,6,2)
--取得结余
select @Number = 0
set nocount on
declare Cur_Table Cursor for 
  select name 
  from sysobjects 
  where name >= 'V200001' and Name <= @startTableName for read only
open Cur_Table
fetch Cur_Table into @tTableName
while @@fetch_status = 0
begin
  select @ttTableName = '##' + right(rtrim(convert(char(40), getdate(),121)),3)
  select @ExecuteString = 'select sum(number) as number into ' 
    + @ttTableName 
    + ' from ' 
    + Rtrim(@tTableName) 
    + ' where V_No = ''' 
    + @V_No 
    + ''' and G_No = ''' 
    + @G_No 
    + ''' and Make_Date < ''' 
    + convert(char(10), @StartDate, 102) 
    + ''''
  exec (@ExecuteString)

  select @ExecuteString = 'declare xxxCur_Table Cursor for select number from ' + @ttTableName
  exec (@ExecuteString)
  open xxxCur_Table
  fetch xxxCur_Table into @tNumber
  close xxxCur_Table
  deallocate xxxCur_Table
  
  select @ExecuteString = 'drop table ' + @ttTableName
  exec (@ExecuteString)

  select @Number = @Number + isnull(@tNumber,0)
  fetch Cur_Table into @tTableName
end
close Cur_Table
deallocate Cur_Table
if isnull(@Number, 0) <> 0
begin
  select @ExecuteString = 'insert ' 
    + RTrim(@TableName) 
    + ' (Make_Date, Summary, Rest_Number) values (''' 
    + convert(char(10), @StartDate, 102) 
    + ''',''期前结余'',' 
    + ltrim(str(@Number)) 
    + ')'
  exec (@ExecuteString)
end
else
begin
 select @Number = 0
end
--取得明细
select @ExecuteString = ''
declare Cur_Table Cursor for 
  select name 
  from sysobjects 
  where name >= @startTableName and Name <= @endTableName for read only
open Cur_Table
fetch Cur_Table into @tTableName
while @@fetch_status = 0
begin
  select @ttTableName = '##' + right(rtrim(convert(char(40), getdate(),121)),3)
  select @ExecuteString = 'select T_No, Make_Date, Summary, Number into ' 
    + @ttTableName 
    + ' from ' 
    + Rtrim(@tTableName) 
    + ' where V_No = ''' 
    + @V_No 
    + ''' and G_No = ''' 
    + @G_No 
    + ''''
  exec (@ExecuteString)

  select @ExecuteString = 'declare tCur_Table Cursor for select T_No, Make_Date, Summary, Number from ' 
    + @ttTableName 
    + ' where Make_Date >= ''' 
    + convert(char(10), @StartDate, 120) 
    + ''' and Make_Date <= ''' 
    + convert(char(10), @EndDate, 120) 
    + ''' order by Make_Date for read only'
  exec (@ExecuteString)

  open tCur_Table
  fetch tCur_Table into @T_No, @Make_Date, @Summary, @In_Number
  while @@fetch_status = 0
  begin
    select @Number = @Number + @In_Number
    if @In_Number >= 0
    begin
      select @ExecuteString = 'insert ' 
        + RTrim(@TableName) 
        + ' (T_No, Make_Date, Summary, In_Number, Rest_Number) values (''' 
        + @T_No + ''',''' 
        + convert(char(10), @Make_Date, 102) 
        + ''',''' 
        + RTrim(@Summary) 
        + ''',' 
        + ltrim(str(@In_Number)) 
        + ',' 
        + ltrim(str(@Number)) + ')'
    end
    else
      select @ExecuteString = 'insert ' 
        + RTrim(@TableName) 
        + ' (T_No, Make_Date, Summary, Out_Number, Rest_Number) values (''' 
        + @T_No 
        + ''',''' 
        + convert(char(10), @Make_Date, 102) 
        + ''',''' 
        + RTrim(@Summary) 
        + ''',' 
        + ltrim(str(-@In_Number)) 
        + ',' 
        + ltrim(str(@Number)) 
        + ')'
    exec (@ExecuteString)
    fetch tCur_Table into @T_No, @Make_Date, @Summary, @In_Number
  end
  close tCur_Table
  deallocate tCur_Table
  select @ExecuteString = 'drop table ' + @ttTableName
  exec (@ExecuteString)

  fetch Cur_Table into @tTableName
end
close Cur_Table
deallocate Cur_Table
go

------------------------------------------------------------
--取得销售客户的金额帐(根据：起止日期、销售客户编号)结果存放在@TableName表中
create procedure Get_ClientMoneyBill 
  (@StartDate datetime, 
  @EndDate datetime, 
  @C_No char(6), 
  @TableName char(20)) WITH ENCRYPTION as
declare @ExecuteString nvarchar(2000)
declare @Amount money
declare @T_No char(12), @Make_Date datetime, @Summary char(10), @In_Amount money
--select @ExecuteString = 'drop table ' + RTrim(@TableName)
--exec (@ExecuteString)
select @ExecuteString = 'create table ' 
  + RTrim(@TableName) 
  + ' (R_No int NOT NULL IDENTITY PRIMARY KEY CLUSTERED,'
  + 'T_No char(12),'
  + 'Make_Date datetime,'
  + 'Summary char(10),'
  + 'In_Amount Money,'
  + 'Out_Amount Money,'
  + 'Rest_Amount Money)'
exec (@ExecuteString)
select @Amount = sum(Sale_Amount) from ClientMoneyBill where C_No = @C_No and Make_Date < @StartDate
if isnull(@Amount, 0) <> 0
begin
  select @ExecuteString = 'insert ' 
    + RTrim(@TableName) 
    + ' (Make_Date, Summary, Rest_Amount) values (''' 
    + convert(char(10), @StartDate, 102) 
    + ''',''期前结余'',' 
    + ltrim(str(@Amount)) 
    + ')'
  exec (@ExecuteString)
end
else
begin
 select @Amount = 0
end
set nocount on
declare Cur_Table Cursor for 
  select T_No, Make_Date, Summary, Sale_Amount 
  from ClientMoneyBill 
  where Make_Date >= @StartDate and Make_Date <= @EndDate and C_No = @C_No 
  order by Make_Date for read only
open Cur_Table
fetch Cur_Table into @T_No, @Make_Date, @Summary, @In_amount
while @@fetch_status = 0
begin
  select @Amount = @Amount + @In_Amount
  if @In_Amount >= 0
  begin
    select @ExecuteString = 'insert ' 
      + RTrim(@TableName) 
      + ' (T_No, Make_Date, Summary, Out_Amount, Rest_Amount) values (''' 
      + @T_No 
      + ''',''' 
      + convert(char(10), @Make_Date, 102) 
      + ''',''' + RTrim(@Summary) 
      + ''',' 
      + ltrim(str(@In_amount)) 
      + ',' 
      + ltrim(str(@Amount)) 
      + ')'
  end
  else
    select @ExecuteString = 'insert ' 
      + RTrim(@TableName) 
      + ' (T_No, Make_Date, Summary, In_Amount, Rest_Amount) values (''' 
      + @T_No 
      + ''',''' 
      + convert(char(10), @Make_Date, 102) 
      + ''',''' 
      + RTrim(@Summary) 
      + ''',' 
      + ltrim(str(-@In_amount)) 
      + ',' 
      + ltrim(str(@Amount)) 
      + ')'
  exec (@ExecuteString)
  fetch Cur_Table into @T_No, @Make_Date, @Summary, @In_amount
end
close Cur_Table
deallocate Cur_Table
go

------------------------------------------------------------
--取得客户的商品帐(根据：起止日期、供应商编号、商品编号)结果存放在@TableName表中
create procedure Get_ClientGoodsBill 
  (@StartDate datetime, 
  @EndDate datetime, 
  @C_No char(6), 
  @G_No char(13), 
  @TableName char(20)) WITH ENCRYPTION as
declare @ExecuteString nvarchar(2000)
declare @Make_Date datetime, @T_No char(12), @Summary char(10)
declare @In_Number float, @Out_Number float, @Rest_Number float
declare @Sale_Number float, @Rtn_Number float

--建表
select @ExecuteString = 'create table ' 
  + RTrim(@TableName) 
  + ' (R_No int NOT NULL IDENTITY PRIMARY KEY CLUSTERED,'
  + 'T_No char(12),'
  + 'Make_Date datetime,'
  + 'Summary char(10),'
  + 'In_Number Float,'
  + 'Out_Number Float,'
  + 'Rest_Number Float)'
exec (@ExecuteString)

--计算期前销售\退货数量
select @Sale_Number = sum(Number) 
  from SaleDtl 
  where G_No = @G_No and T_No in (select T_No from SaleMst where C_No = @C_No and Attrib = 1 and Make_Date < @StartDate)
select @Rtn_Number = sum(Number)