UC知道关于MSSQL存储过程中数组问题
来源：百度知道 编辑：UC知道 时间：2024/06/16 02:02:41
代码:
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
怎么表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