set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER PROCEDURE [dbo].[proc_InsertASKPrice]
(
@askpricenum nvarchar(50),
@askname nvarchar(50),
@askcompany nvarchar(50),
@askphone nvarchar(50),
@askfex nvarchar(50),
@askre nvarchar(4000),
@productliststr nvarchar(2500)
)
AS
BEGIN
begin tran
declare @V_LENGTHNUM int,@V_LENGTH int,@V_POS int,@V_CURRENT nvarchar(205),
@V_TEMP nvarchar(2500),@productname nvarchar(50),@productsize nvarchar(50),
@productnum nvarchar(50),@productre nvarchar(200) insert into askpriceinfo(askpricenum,askname,askcompany,askphone,askfex,askre) values(@askpricenum,@askname,@askcompany,@askphone,@askfex,@askre) if @@error!=0
begin
rollback tran
return
end
-- productliststr='..|..|..|...#..|..|..|...#'; SET @V_LENGTH=len(@productliststr);
set @V_LENGTHNUM=len(@productliststr); while @V_LENGTH>0
begin
SET @V_POS=charindex(@V_TEMP,'#');
SET @V_CURRENT=substring(@V_TEMP,1,@V_POS-1);
SET @V_TEMP=substring(@V_TEMP,@V_POS+1,len(@productliststr)-@V_POS+1);
SET @V_LENGTH=len(@V_TEMP); SET @V_POS=charindex(@V_CURRENT, '|');
SET @productname=substring(@V_CURRENT,1,@V_POS-1);
SET @V_CURRENT=substring(@V_CURRENT,@V_POS+1,len(@V_CURRENT)-@V_POS+1);
SET @V_POS=charindex(@V_CURRENT, '|');
SET @productsize=substring(@V_CURRENT,1,@V_POS-1);
SET @V_CURRENT=substring(@V_CURRENT,@V_POS+1,len(@V_CURRENT)-@V_POS+1); SET @V_POS=charindex(@V_CURRENT, '|');
SET @productnum=substring(@V_CURRENT,1,@V_POS-1);
SET @V_CURRENT=substring(@V_CURRENT,@V_POS+1,len(@V_CURRENT)-@V_POS+1);
SET @productre=@V_CURRENT;
insert into askpriceDetails(askpricenum,productname,productsize,productnum,productre) values(@askpricenum,@productname,@productsize,@productnum,@productre) if @@error!=0
begin
rollback tran
return
end
end commit tranEND
set QUOTED_IDENTIFIER ON
goALTER PROCEDURE [dbo].[proc_InsertASKPrice]
(
@askpricenum nvarchar(50),
@askname nvarchar(50),
@askcompany nvarchar(50),
@askphone nvarchar(50),
@askfex nvarchar(50),
@askre nvarchar(4000),
@productliststr nvarchar(2500)
)
AS
BEGIN
begin tran
declare @V_LENGTHNUM int,@V_LENGTH int,@V_POS int,@V_CURRENT nvarchar(205),
@V_TEMP nvarchar(2500),@productname nvarchar(50),@productsize nvarchar(50),
@productnum nvarchar(50),@productre nvarchar(200) insert into askpriceinfo(askpricenum,askname,askcompany,askphone,askfex,askre) values(@askpricenum,@askname,@askcompany,@askphone,@askfex,@askre) if @@error!=0
begin
rollback tran
return
end
-- productliststr='..|..|..|...#..|..|..|...#'; SET @V_LENGTH=len(@productliststr);
set @V_LENGTHNUM=len(@productliststr); while @V_LENGTH>0
begin
SET @V_POS=charindex(@V_TEMP,'#');
SET @V_CURRENT=substring(@V_TEMP,1,@V_POS-1);
SET @V_TEMP=substring(@V_TEMP,@V_POS+1,len(@productliststr)-@V_POS+1);
SET @V_LENGTH=len(@V_TEMP); SET @V_POS=charindex(@V_CURRENT, '|');
SET @productname=substring(@V_CURRENT,1,@V_POS-1);
SET @V_CURRENT=substring(@V_CURRENT,@V_POS+1,len(@V_CURRENT)-@V_POS+1);
SET @V_POS=charindex(@V_CURRENT, '|');
SET @productsize=substring(@V_CURRENT,1,@V_POS-1);
SET @V_CURRENT=substring(@V_CURRENT,@V_POS+1,len(@V_CURRENT)-@V_POS+1); SET @V_POS=charindex(@V_CURRENT, '|');
SET @productnum=substring(@V_CURRENT,1,@V_POS-1);
SET @V_CURRENT=substring(@V_CURRENT,@V_POS+1,len(@V_CURRENT)-@V_POS+1);
SET @productre=@V_CURRENT;
insert into askpriceDetails(askpricenum,productname,productsize,productnum,productre) values(@askpricenum,@productname,@productsize,@productnum,@productre) if @@error!=0
begin
rollback tran
return
end
end commit tranEND
好像没有设置这个循环控制变量的增量吧
加上set @V_LENGTH=@V_LENGTH-1
试试
--看了下楼主的这个:SET @V_POS=charindex(@V_CURRENT, ' ¦');
楼主是不是写反了?应该:SET @V_POS=charindex(' ¦', @V_CURRENT);
是插入到askpriceDetails还是askpriceinfo?
askpriceDetails中askpricenum应该是主键吧,那么循环的时候,主键是不是会重复?如果重复的话那么就会产生错误,
而楼主的pcocedure里面,会进行回滚,那么也就不会向askpriceDetails中插入
set QUOTED_IDENTIFIER ON
goALTER PROCEDURE [dbo].[proc_InsertASKPrice]
(
@askpricenum nvarchar(50),
@askname nvarchar(50),
@askcompany nvarchar(50),
@askphone nvarchar(50),
@askfex nvarchar(50),
@askre nvarchar(4000),
@productliststr nvarchar(2500)
)
AS
BEGIN
begin tran
declare @V_LENGTH int,@V_POS int,@V_CURRENT nvarchar(205),
@V_TEMP nvarchar(2500),@productname nvarchar(50),@productsize nvarchar(50),
@productnum nvarchar(50),@productre nvarchar(200) insert into askpriceinfo(askpricenum,askname,askcompany,askphone,askfex,askre) values(@askpricenum,@askname,@askcompany,@askphone,@askfex,@askre) if @@error!=0
begin
rollback tran
return
end
-- productliststr='..|..|..|...#..|..|..|...'; SET @V_LENGTH=len(@productliststr);
set @V_TEMP=@productliststr; while @V_LENGTH>0
begin
SET @V_POS=charindex('#',@V_TEMP);
SET @V_CURRENT=substring(@V_TEMP,1,@V_POS-1);
SET @V_TEMP=substring(@V_TEMP,@V_POS+1,len(@V_TEMP)-@V_POS+1);
SET @V_LENGTH=len(@V_TEMP); SET @V_POS=charindex( '|',@V_CURRENT);
SET @productname=substring(@V_CURRENT,1,@V_POS-1);
SET @V_CURRENT=substring(@V_CURRENT,@V_POS+1,len(@V_CURRENT)-@V_POS+1);
SET @V_POS=charindex('|',@V_CURRENT );
SET @productsize=substring(@V_CURRENT,1,@V_POS-1);
SET @V_CURRENT=substring(@V_CURRENT,@V_POS+1,len(@V_CURRENT)-@V_POS+1); SET @V_POS=charindex( '|',@V_CURRENT);
SET @productnum=substring(@V_CURRENT,1,@V_POS-1);
SET @V_CURRENT=substring(@V_CURRENT,@V_POS+1,len(@V_CURRENT)-@V_POS+1);
SET @productre=@V_CURRENT;
insert into askpriceDetails(askpricenum,productname,productsize,productnum,productre) values(@askpricenum,@productname,@productsize,@productnum,@productre) if @@error!=0
begin
rollback tran
return
end
end commit tranEND我改成 这样了 我获得异常是 substring 的 参数 无效 你们看看啊
是不是 这句出错了 @V_TEMP=substring(@V_TEMP,@V_POS+1,len(@V_TEMP)-@V_POS+1)
len(@V_TEMP)-@V_POS+1 这个 是不是 出问题了