看我的存贮过程 怎么 不循环 插入 啊 好

[wanghao3616]

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go







ALTER PROCEDURE [dbo].[proc_InsertASKPrice]
(
@askpricenum nvarchar(50),
@askname nvarchar(50),
@askcompany nvarchar(50),
@askphone nvarchar(50),
@askfex nvarchar(50),
@askremark 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),@productremark nvarchar(200)

insert into askpriceinfo(askpricenum,askname,askcompany,askphone,askfex,askremark) values(@askpricenum,@askname,@askcompany,@askphone,@askfex,@askremark)

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 @productremark=@V_CURRENT;

insert into askpriceDetails(askpricenum,productname,productsize,productnum,productremark) values(@askpricenum,@productname,@productsize,@productnum,@productremark)

if @@error!=0
begin
rollback tran
return
end
end

commit tran

END

[lff642]

现在是插入一次就完成了吗?

[wanghao3616]

对 它 只插了 一条 还 只插了 askpricenum 其他的 为 null

[让你望见影子的墙]

@V_LENGTH
好像没有设置这个循环控制变量的增量吧
加上set @V_LENGTH=@V_LENGTH-1
试试

[pt1314917]

--看了下楼主的这个：SET  @V_POS=charindex(@V_CURRENT, ' ¦'); 
楼主是不是写反了？应该：SET  @V_POS=charindex(' ¦', @V_CURRENT); 

[让你望见影子的墙]

[Quote=引用 2 楼 wanghao3616 的回复:]
对 它 只插了 一条 还 只插了 askpricenum 其他的 为 null
[/Quote]

是插入到askpriceDetails还是askpriceinfo？

[让你望见影子的墙]

另，觉得楼主的还有一个问题
askpriceDetails中askpricenum应该是主键吧，那么循环的时候，主键是不是会重复？如果重复的话那么就会产生错误，
而楼主的pcocedure里面，会进行回滚，那么也就不会向askpriceDetails中插入

[wanghao3616]

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go









ALTER PROCEDURE [dbo].[proc_InsertASKPrice]
(
@askpricenum nvarchar(50),
@askname nvarchar(50),
@askcompany nvarchar(50),
@askphone nvarchar(50),
@askfex nvarchar(50),
@askremark 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),@productremark nvarchar(200)

insert into askpriceinfo(askpricenum,askname,askcompany,askphone,askfex,askremark) values(@askpricenum,@askname,@askcompany,@askphone,@askfex,@askremark)

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 @productremark=@V_CURRENT;

insert into askpriceDetails(askpricenum,productname,productsize,productnum,productremark) values(@askpricenum,@productname,@productsize,@productnum,@productremark)

if @@error!=0
begin
rollback tran
return
end
end

commit tran

END

我改成 这样了 我获得异常是 substring 的 参数 无效 你们看看啊
是不是 这句出错了 @V_TEMP=substring(@V_TEMP,@V_POS+1,len(@V_TEMP)-@V_POS+1)
len(@V_TEMP)-@V_POS+1 这个 是不是 出问题了