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()
declare @storagenum varchar() --仓库代码
declare @localnum varchar() --库区代码
declare @goodlocationname varchar() --货位名称
declare @goodlocationtype varchar() --货位类型
declare @goodlocationnum varchar() --货位代码
declare @roadway_num varchar()
declare @cargo_area_id varchar()
declare @cargo_space_name varchar()
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(''+rtrim(@the_plattoon) as varchar()),),'排',right(cast(''+rtrim(@the_column) as varchar()),),'列',right(cast(''+rtrim(@the_floor) as varchar()),),'层')
--取得巷道代码
select @roadway_num=cast(round(cast(cast(@the_plattoon as decimal(,))/ as decimal(,)),) as int)
--判断该条记录是否已存在
select @count=count(*) from [dbo].[Sys_GoodLocation] where F_GoodLocationName=@goodlocationname
--取得货位代码
select @goodlocationnum= concat('',right(cast(''+rtrim(@the_plattoon) as varchar()),),right(cast(''+rtrim(@the_column) as varchar()),),right(cast(''+rtrim(@the_floor) as varchar()),)) --插入语句
if @count=
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
,
,@goodlocationname
,@the_plattoon
,@the_column
,@the_floor
,
,'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=
while @initplatoon<=@the_plattoon --循环排
begin
set @initcolumn=
while @initcolumn<=@the_column --循环列
begin
set @initfloor=
while @initfloor<=@the_floor --循环层
begin
print concat(@initplatoon,@initcolumn,@initfloor)
exec [dbo].[sp_insert_goodlocation] @initplatoon,@initcolumn,@initfloor --插入
set @initfloor=@initfloor+
end
set @initcolumn=@initcolumn+
end
set @initplatoon=@initplatoon+
end
end
END
commit tran
END try
begin catch
rollback tran
end catch
Execute：
exec sp_batchinsert_goodlocation ,,
Result：