Init wms goodlocation data
insert goodlocation:
CREATE PROCEDURE [dbo].[sp_insert_goodlocation] -- Add the parameters for the stored procedure here @the_plattoon int, @the_column int, @the_floor int AS begin try begin tran BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; declare @count int declare @cargo_space_id varchar(30) declare @storagenum varchar(10) --仓库代码 declare @localnum varchar(20) --库区代码 declare @goodlocationname varchar(30) --货位名称 declare @goodlocationtype varchar(30) --货位类型 declare @goodlocationnum varchar(30) --货位代码 declare @roadway_num varchar(30) declare @cargo_area_id varchar(30) declare @cargo_space_name varchar(30) begin --取得仓库代码 SELECT @storagenum=F_StorageNum FROM [dbo].[Sys_Storage] --取得库区代码 select @localnum=F_LocalNum from Sys_Location where F_StorageNum=@storagenum --取得货位名称 select @goodlocationname=concat(right(cast('00'+rtrim(@the_plattoon) as varchar(20)),2),'排',right(cast('00'+rtrim(@the_column) as varchar(20)),2),'列',right(cast('00'+rtrim(@the_floor) as varchar(20)),2),'层') --取得巷道代码 select @roadway_num=cast(round(cast(cast(@the_plattoon as decimal(8,2))/2 as decimal(8,2)),0) as int) --判断该条记录是否已存在 select @count=count(*) from [dbo].[Sys_GoodLocation] where F_GoodLocationName=@goodlocationname --取得货位代码 select @goodlocationnum= concat('01',right(cast('00'+rtrim(@the_plattoon) as varchar(20)),2),right(cast('00'+rtrim(@the_column) as varchar(20)),2),right(cast('00'+rtrim(@the_floor) as varchar(20)),2)) --插入语句 if @count=0 begin INSERT INTO [dbo].[Sys_GoodLocation] ([F_GoodLocationNum] ,[F_StorageNum] ,[F_RoadWayNum] ,[F_LocalNum] ,[F_GoodLocationType] ,[F_GoodLocationName] ,[F_Platoon] ,[F_Column] ,[F_Floor] ,[F_Status] ,[F_Locked] ,[F_TrayCode] ,[F_TrayInTime] ,[F_JobId] ,[F_TaskNo] ,[F_JobInTime] ,[F_SortCode] ,[F_DeleteMark] ,[F_EnabledMark] ,[F_Description] ,[F_CreatorTime] ,[F_CreatorUserId] ,[F_LastModifyTime] ,[F_LastModifyUserId] ,[F_DeleteTime] ,[F_DeleteUserId]) VALUES (@goodlocationnum ,@storagenum ,@roadway_num ,@localnum ,1 ,@goodlocationname ,@the_plattoon ,@the_column ,@the_floor ,1 ,'N' ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL) end end END commit tran END try begin catch rollback tran end catch
sp_batchinsert_goodlocation:
CREATE PROCEDURE [dbo].[sp_batchinsert_goodlocation] -- Add the parameters for the stored procedure here @the_plattoon int, @the_column int, @the_floor int AS begin try BEGIN TRANSACTION BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; declare @initplatoon int declare @initcolumn int declare @initfloor int begin set @initplatoon=1 while @initplatoon<=@the_plattoon --循环排 begin set @initcolumn=1 while @initcolumn<=@the_column --循环列 begin set @initfloor=1 while @initfloor<=@the_floor --循环层 begin print concat(@initplatoon,@initcolumn,@initfloor) exec [dbo].[sp_insert_goodlocation] @initplatoon,@initcolumn,@initfloor --插入 set @initfloor=@initfloor+1 end set @initcolumn=@initcolumn+1 end set @initplatoon=@initplatoon+1 end end END commit tran END try begin catch rollback tran end catch
Execute:
exec sp_batchinsert_goodlocation 8,37,5
Result: