CREATE PROCEDURE Create_io_stock AS
declare @stock varchar(7)
declare @stock2 varchar(7)
set @stock = "i"+left(convert(char,dateadd(m,1,getdate()),112),6)
set @stock2 = "o"+left(convert(char,dateadd(m,1,getdate()),112),6)
declare @companyid int
declare @str varchar(1000)
declare @companycount int
declare company_cur scroll cursor
for select company_id from company
open company_cur
/*select hubin = @@cursor_rows*/
fetch first from company_cur into @companyid
while @@fetch_status = 0
begin
/*
set @str = 'select ' + @companycount +' = count(*) from io_company where company_id = '
set @str = @str + @companyid +' and company_table = '
set @str = @str + rtrim(@stock+convert(varchar(20),@companyid))
exec(@str)
if @companycount = 0
*/
begin
/*建立各个公司入库条码表*/
set @str = "CREATE TABLE " + rtrim(@stock+convert(varchar(20),@companyid)) + " ("
set @str = @str + "[in_info_id] [int] IDENTITY (1, 1) NOT NULL ,"
set @str = @str + "[in_inwa_id] [int] NOT NULL ,"
set @str = @str + "[in_idetail_id] [int] NOT NULL ,"
set @str = @str + "[in_info_xianghao] [varchar] (50) NULL ,"
set @str = @str + "[in_info_code] [varchar] (50) NULL ,"
set @str = @str + "[in_info_dc_id] [int] NOT NULL ,"
set @str = @str + "[in_info_prod_id] [int] NOT NULL ,"
set @str = @str + "[in_info_companyid] [int] NOT NULL ,"
set @str = @str + "[in_info_date] [datetime] NULL "
set @str = @str + ") ON [PRIMARY]"
exec(@str)
insert into io_company(company_id,company_table,company_date,io_flag) values(@companyid,rtrim(@stock+convert(varchar(20),@companyid)),getdate(),1)
/*建立各个公司出库条码表*/
set @str = "CREATE TABLE " + rtrim(@stock2+convert(varchar(20),@companyid)) + " ("
set @str = @str + "[out_info_id] [int] IDENTITY (1, 1) NOT NULL ,"
set @str = @str + "[out_elink_id] [int] NOT NULL ,"
set @str = @str + "[out_detail_id] [int] NULL ,"
set @str = @str + "[out_dc_id] [int] NULL ,"
set @str = @str + "[out_prod_id] [int] NULL ,"
set @str = @str + "[out_companyid] [int] NULL ,"
set @str = @str + "[out_info_xianghao] [varchar] (50) NULL ,"
set @str = @str + "[out_info_code] [varchar] (50) NULL ,"
set @str = @str + "[out_date] [datetime] NULL "
set @str = @str + ") ON [PRIMARY]"
exec(@str)
insert into io_company(company_id,company_table,company_date,io_flag) values(@companyid,rtrim(@stock2+convert(varchar(20),@companyid)),getdate(),2)
end
fetch next from company_cur into @companyid
end
close company_cur
deallocate company_cur