--2
create proc mytest1
(
@str varchar(50)
)
as
declare @tmp varchar(50)
while(charindex(',',@str)>0)
begin
set @tmp=Substring(@str,1,Charindex(',',@str)-1)
set @str=substring(@str,charindex(',',@str)+1,len(@str))
select @tmp,@str
end
go
create proc mytest1
(
@str varchar(50)
)
as
declare @tmp varchar(50)
while(charindex(',',@str)>0)
begin
set @tmp=Substring(@str,1,Charindex(',',@str)-1)
set @str=substring(@str,charindex(',',@str)+1,len(@str))
select @tmp,@str
end
go
alter proc mytest
(
@str varchar(50)
)
as
declare @tmp varchar(50)
while(len(@str)>0)
begin
if charindex(',',@str)<>0
begin
set @tmp=Substring(@str,1,Charindex(',',@str)-1)
set @str=substring(@str,charindex(',',@str)+1,len(@str))
end
else
begin
break
end
select @tmp,@str
end
go
(
@str varchar(50)
)
as
declare @tmp varchar(50)
while(len(@str)>0)
begin
if charindex(',',@str)<>0
begin
set @tmp=Substring(@str,1,Charindex(',',@str)-1)
set @str=substring(@str,charindex(',',@str)+1,len(@str))
end
else
begin
break
end
select @tmp,@str
end
go
exec mytest 'aaaaa,bbbb,ccc,dd,eeeee,fffff,ggggg'
自己试验的一个实例:
/*入库存储过程*/
alter proc Store_In
(
--库单部分
@CreateDate datetime,
@UserID int,
--明细部分
@ProductID varchar(4000), --商品ID,主码
@BatdchCode varchar(4000), --次条码
@IOPrice varchar(4000), --价格
@IOAmount varchar(1000), --数量
--库单主键
@IOId int output --订单ID
)
as
begin tran store_in_commit
--插入入库表一条信息
insert into StoreInOut(Flat,CreateDate,UserID) values(1,@CreateDate,@UserID)
select @IOId=@@identity
--插入到明细
declare @count int
declare @tmp_ProductID varchar(4000) --商品ID临时
declare @tmp_BatdchCode varchar(4000) --次条码临时
declare @tmp_IOPrice varchar(4000) --价格临时
declare @tmp_IOAmount varchar(1000) --数量临时
declare @count_BatdchCode int --商品次码计数
declare @count_IOPrice int --价格计数
declare @count_IOAmount int --数量技术
declare @price money
set @count=Charindex(',',@ProductID) --查找','出现在@str这个字符串第一次的位置
while @count>0
begin
--商品ID
set @tmp_ProductID=substring(@ProductID,1,(@count-1)) --这个是要插入到表的数据
set @ProductID=substring(@ProductID,(@count+1),(len(@ProductID)-@count))
set @count=charindex(',',@ProductID)
--次条码
set @count_BatdchCode=Charindex(',',@BatdchCode)
set @tmp_BatdchCode=substring(@BatdchCode,1,(@count_BatdchCode-1)) --这个是要插入到表的数据
set @BatdchCode=substring(@BatdchCode,(@count_BatdchCode+1),(len(@BatdchCode)-@count_BatdchCode))
--价格
set @count_IOPrice=Charindex(',',@IOPrice)
set @tmp_IOPrice=substring(@IOPrice,1,(@count_IOPrice-1)) --这个是要插入到表的数据
set @IOPrice=substring(@IOPrice,(@count_IOPrice+1),(len(@IOPrice)-@count_IOPrice))
--数量
set @count_IOAmount=Charindex(',',@IOAmount)
set @tmp_IOAmount=substring(@IOAmount,1,(@count_IOAmount-1)) --这个是要插入到表的数据
set @IOAmount=substring(@IOAmount,(@count_IOAmount+1),(len(@IOAmount)-@count_IOAmount))
set @price=cast(@tmp_IOPrice as money)
insert into IOStoreInfo(IOId,
ProductID,
BatdchCode,
IOPrice,
IOAmount,
Flat)
values(@IOId,
@tmp_ProductID,
@tmp_BatdchCode,
@price,
cast(@tmp_IOAmount as int),
1) --入库
end
--插最后一个明细
set @price=cast(@IOPrice as money)
insert into IOStoreInfo(IOId,
ProductID,
BatdchCode,
IOPrice,
IOAmount,
Flat)
values(@IOId,
@ProductID,
@BatdchCode,
@price,
cast(@IOAmount as int),
1) --入库
--select @ProductID --最后一个插入,商品
commit tran store_in_commit
alter proc Store_In
(
--库单部分
@CreateDate datetime,
@UserID int,
--明细部分
@ProductID varchar(4000), --商品ID,主码
@BatdchCode varchar(4000), --次条码
@IOPrice varchar(4000), --价格
@IOAmount varchar(1000), --数量
--库单主键
@IOId int output --订单ID
)
as
begin tran store_in_commit
--插入入库表一条信息
insert into StoreInOut(Flat,CreateDate,UserID) values(1,@CreateDate,@UserID)
select @IOId=@@identity
--插入到明细
declare @count int
declare @tmp_ProductID varchar(4000) --商品ID临时
declare @tmp_BatdchCode varchar(4000) --次条码临时
declare @tmp_IOPrice varchar(4000) --价格临时
declare @tmp_IOAmount varchar(1000) --数量临时
declare @count_BatdchCode int --商品次码计数
declare @count_IOPrice int --价格计数
declare @count_IOAmount int --数量技术
declare @price money
set @count=Charindex(',',@ProductID) --查找','出现在@str这个字符串第一次的位置
while @count>0
begin
--商品ID
set @tmp_ProductID=substring(@ProductID,1,(@count-1)) --这个是要插入到表的数据
set @ProductID=substring(@ProductID,(@count+1),(len(@ProductID)-@count))
set @count=charindex(',',@ProductID)
--次条码
set @count_BatdchCode=Charindex(',',@BatdchCode)
set @tmp_BatdchCode=substring(@BatdchCode,1,(@count_BatdchCode-1)) --这个是要插入到表的数据
set @BatdchCode=substring(@BatdchCode,(@count_BatdchCode+1),(len(@BatdchCode)-@count_BatdchCode))
--价格
set @count_IOPrice=Charindex(',',@IOPrice)
set @tmp_IOPrice=substring(@IOPrice,1,(@count_IOPrice-1)) --这个是要插入到表的数据
set @IOPrice=substring(@IOPrice,(@count_IOPrice+1),(len(@IOPrice)-@count_IOPrice))
--数量
set @count_IOAmount=Charindex(',',@IOAmount)
set @tmp_IOAmount=substring(@IOAmount,1,(@count_IOAmount-1)) --这个是要插入到表的数据
set @IOAmount=substring(@IOAmount,(@count_IOAmount+1),(len(@IOAmount)-@count_IOAmount))
set @price=cast(@tmp_IOPrice as money)
insert into IOStoreInfo(IOId,
ProductID,
BatdchCode,
IOPrice,
IOAmount,
Flat)
values(@IOId,
@tmp_ProductID,
@tmp_BatdchCode,
@price,
cast(@tmp_IOAmount as int),
1) --入库
end
--插最后一个明细
set @price=cast(@IOPrice as money)
insert into IOStoreInfo(IOId,
ProductID,
BatdchCode,
IOPrice,
IOAmount,
Flat)
values(@IOId,
@ProductID,
@BatdchCode,
@price,
cast(@IOAmount as int),
1) --入库
--select @ProductID --最后一个插入,商品
commit tran store_in_commit