我现在数据库中有个表主键列是库位的编码
1D03071 第一位代表跨(范围1-4)第二位代表区(范围A-H)3、4位代表行(1-25),五六位代表列(1-15)最后一位表示在1层还是2层
每个区行和列的数量不定,使用sql语句实现将所有的库位编码填到数据库中,我写了个存储过程根本不行
create proc proc_StorageMaker
@Step varchar(1),
@Area varchar(1),
@RowCount int,
@LineCount int
as
declare @RowCountTemp int
declare @LineCountTemp int
set @RowCountTemp = @RowCount
while(@RowCount>0)
begin
while(@LineCount>0)
begin
insert dbo.T_Bas_LPNS_StorageTest (storage_id)
values @Step+@Area+case when @RowCount<10 then '0'+cast(@RowCount as varchar(1))
else cast(@RowCount as varchar(2))+
case when @LineCount<10 then '0'+cast(@LineCount as varchar(1))
else cast(@LineCount as varchar(2))+'1'
if(@RowCount != @RowCountTemp)
begin
insert dbo.T_Bas_LPNS_StorageTest (storage_id)
values @Step+@Area+case when @RowCount<10 then '0'+cast(@RowCount as varchar(1))
else cast(@RowCount as varchar(2)) end+ case when @LineCount<10 then '0'+cast(@LineCount as varchar(1))
else cast(@LineCount as varchar(2)) end+'2'
end
set @LineCount = @LineCount-1
end
set @RowCount = @RowCount-1
end
语法检查通不过,“服务器:消息 170,级别 15” 我想问这句话到底什么意思,从哪能知道错误的原因,比如170在哪能查到它的解释
1D03071 第一位代表跨(范围1-4)第二位代表区(范围A-H)3、4位代表行(1-25),五六位代表列(1-15)最后一位表示在1层还是2层
每个区行和列的数量不定,使用sql语句实现将所有的库位编码填到数据库中,我写了个存储过程根本不行
create proc proc_StorageMaker
@Step varchar(1),
@Area varchar(1),
@RowCount int,
@LineCount int
as
declare @RowCountTemp int
declare @LineCountTemp int
set @RowCountTemp = @RowCount
while(@RowCount>0)
begin
while(@LineCount>0)
begin
insert dbo.T_Bas_LPNS_StorageTest (storage_id)
values @Step+@Area+case when @RowCount<10 then '0'+cast(@RowCount as varchar(1))
else cast(@RowCount as varchar(2))+
case when @LineCount<10 then '0'+cast(@LineCount as varchar(1))
else cast(@LineCount as varchar(2))+'1'
if(@RowCount != @RowCountTemp)
begin
insert dbo.T_Bas_LPNS_StorageTest (storage_id)
values @Step+@Area+case when @RowCount<10 then '0'+cast(@RowCount as varchar(1))
else cast(@RowCount as varchar(2)) end+ case when @LineCount<10 then '0'+cast(@LineCount as varchar(1))
else cast(@LineCount as varchar(2)) end+'2'
end
set @LineCount = @LineCount-1
end
set @RowCount = @RowCount-1
end
语法检查通不过,“服务器:消息 170,级别 15” 我想问这句话到底什么意思,从哪能知道错误的原因,比如170在哪能查到它的解释
create proc proc_StorageMaker
@Step varchar(1),
@Area varchar(1),
@RowCount int,
@LineCount int
as
declare @RowCountTemp int
declare @LineCountTemp int
set @RowCountTemp = @RowCount
while(@RowCount>0)
begin
while(@LineCount>0)
begin
insert dbo.T_Bas_LPNS_StorageTest (storage_id)
select @Step+@Area+case when @RowCount <10 then '0'+cast(@RowCount as varchar(1))
else cast(@RowCount as varchar(2))+
case when @LineCount <10 then '0'+cast(@LineCount as varchar(1))
else cast(@LineCount as varchar(2))+'1' end end
if(@RowCount != @RowCountTemp)
begin
insert dbo.T_Bas_LPNS_StorageTest (storage_id)
select @Step+@Area+case when @RowCount <10 then '0'+cast(@RowCount as varchar(1))
else cast(@RowCount as varchar(2)) end+ case when @LineCount <10 then '0'+cast(@LineCount as varchar(1))
else cast(@LineCount as varchar(2)) end+'2'
end
set @LineCount = @LineCount-1
end
set @RowCount = @RowCount-1
end
改成这样
@Step varchar(1),
@Area varchar(1),
@RowCount int,
@LineCount int
as
begin
declare @RowCountTemp int
declare @LineCountTemp int
set @RowCountTemp = @RowCount
while(@RowCount>0)
begin
while(@LineCount>0)
begin
insert dbo.T_Bas_LPNS_StorageTest (storage_id)
values @Step+@Area+ right('0'+cast(@RowCount as varchar(2)),2) +
right('0'+cast(@LineCount as varchar(2)),2) +'1'
if(@RowCount != @RowCountTemp)
begin
insert dbo.T_Bas_LPNS_StorageTest (storage_id)
values @Step+@Area+right('0'+cast(@RowCount as varchar(2)),2) +
right('0'+cast(@LineCount as varchar(2)),2) +'2'
set @LineCount = @LineCount-1
end
set @RowCount = @RowCount-1
end
end
go
else cast(@RowCount as varchar(2)) end
--------------right('0'+cast(@RowCount as varchar(2)) ,2)
@Step varchar(1),
@Area varchar(1),
@RowCount int,
@LineCount int
as
declare @RowCountTemp int
declare @LineCountTemp int
set @RowCountTemp = @RowCount
while(@RowCount>0)
begin
while(@LineCount>0)
begin
insert dbo.T_Bas_LPNS_StorageTest (storage_id)
values (@Step+@Area+(case when @RowCount <10 then '0'+cast(@RowCount as varchar(1))
else cast(@RowCount as varchar(2)) end )+ (
case when @LineCount <10 then '0'+cast(@LineCount as varchar(1))
else cast(@LineCount as varchar(2)) end)+'1')
if(@RowCount != @RowCountTemp)
begin
insert dbo.T_Bas_LPNS_StorageTest (storage_id)
values (@Step+@Area+(case when @RowCount <10 then '0'+cast(@RowCount as varchar(1))
else cast(@RowCount as varchar(2)) end)+ (case when @LineCount <10 then '0'+cast(@LineCount as varchar(1))
else cast(@LineCount as varchar(2)) end)+'2' )
end
set @LineCount = @LineCount-1
end
set @RowCount = @RowCount-1
end
消息 102,级别 15,状态 1,过程 proc_StorageMaker,第 16 行
'@Step' 附近有语法错误。
消息 102,级别 15,状态 1,过程 proc_StorageMaker,第 21 行
'@Step' 附近有语法错误。
消息 102,级别 15,状态 1,过程 proc_StorageMaker,第 27 行
'end' 附近有语法错误。
create table T_Bas_LPNS_StorageTest(storage_id varchar(80))
gocreate proc proc_StorageMaker
@Step varchar(1),
@Area varchar(1),
@RowCount int,
@LineCount int
as
begin
declare @RowCountTemp int
declare @LineCountTemp int
set @RowCountTemp = @RowCount
while(@RowCount>0)
begin
while(@LineCount>0)
begin
insert dbo.T_Bas_LPNS_StorageTest (storage_id)
select @Step+@Area+ right('00'+cast(@RowCount as varchar ),2) +
right('00'+cast(@LineCount as varchar ),2) +'1'
if(@RowCount != @RowCountTemp)
begin
insert dbo.T_Bas_LPNS_StorageTest (storage_id)
select @Step+@Area+right('00'+cast(@RowCount as varchar ),2) +
right('00'+cast(@LineCount as varchar ),2) +'2'
set @LineCount = @LineCount-1
end
set @RowCount = @RowCount-1
end
end
end
go
exec proc_StorageMaker '1','1',2,11select * from T_Bas_LPNS_StorageTest
drop proc proc_StorageMaker
drop table T_Bas_LPNS_StorageTest