关于MSSQL存储过程中数组问题

来源：百度知道 编辑：UC知道 时间：2024/06/16 07:43:27
代码:
CREATE PROCEDURE SelectArray
@id int,
@str varchar(250)
AS
declare @value int
declare @i int
declare @sql varchar(200)
-- delete from [id2] where id1=@id
while len(@str) > 0
begin
SELECT @i = Charindex(';',@str)
set @value = cast(SUBSTRING ( @str , 1 , @i-1 ) as int)
print @value
insert into [id2](id1,id2)values(@id,@value)
set @str = SUBSTRING ( @str , @i+1 , len(@str)-@i )
end
GO

怎么表id2中插入不了数据,表结构很简单,id,id1,id2均为int类型.测试输入参数:@str='1;2;3;4;56;5;8;'请高手在此基础上解惑!

不问题可以插入

CREATE PROCEDURE SelectArray
@id int,
@str varchar(250)
AS
declare @value int
declare @i int
declare @sql varchar(200)
-- delete from [id2] where id1=@id
while len(@str) > 0
begin
SELECT @i = Charindex(';',@str)
set @value = cast(SUBSTRING ( @str , 1 , @i-1 ) as int)
print @value
insert into [id2](id1,id2)values(@id,@value)
set @str = SUBSTRING ( @str , @i+1 , len(@str)-@i )
end
GO
create table [id2](id1 int,id2 int)

exec SelectArray 1,'1;2;3;4;56;5;8;'

go
select * from [id2]
id1 id2
----------- -----------
1 1
1 2
1 3
1 4
1 56
1 5
1 8

(7 行受影响)

存储过程中数组问题，但参数字符超过8000时，可以用text做参数，再转换成数组

CREATE PROCEDURE SelectArray
@id int,
@str text
AS
declare @val