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

发布网友发布时间：2022-04-27 09:49

共2个回答

热心网友时间：2022-04-08 11:21

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

CREATE PROCEDURE SelectArray
@id int,
@str text
AS
declare @value int
declare @i int
declare @sql varchar(200)
-- delete from [id2] where id1=@id
while DATALENGTH(@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 , DATALENGTH(@str)-@i )
end
GO
create table [id2](id1 int,id2 int)

exec SelectArray 1,'10001;10002;10003;10004;100056;10005;10008;'

go
select * from [id2]

热心网友时间：2022-04-08 12:39

不问题可以插入

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 行受影响)