存储过程处理数组
1 create procedure sp_split_string
2 (
3 @string NVARCHAR(4000)
4 )
5 AS
6 BEGIN
7 DECLARE @object_id nvarchar(500)
8 DECLARE @i INT
9 DECLARE @len INT
10 print @string
11 IF (@string IS NULL) OR (LTRIM(@string) = '')
12 RETURN
13
14 WHILE CHARINDEX(',',@string) > 0
15 BEGIN
16 SET @len = LEN(@string)
17 SET @i = CHARINDEX(',', @string)
18 SET @object_id = LEFT(@string, @i-1)
19 INSERT INTO a (id) VALUES (@object_id)--少做修改,改成需要的sql语句即可
20 SET @string = RIGHT(@string, @len - @i)
21 END
22 SET @object_id = @string
23 INSERT INTO a (id) VALUES (@object_id)--少做修改,改成需要的sql语句即可
24 END
25 go
26
27
28 --测试
29 create table a
30 (
31 id int
32 )
33 select * from a
34 exec sp_split_string '102,103,105,106,107,108,200,500,306,408'
35 select * from a 向存储过程中传数组字符串(并存储过程字符串的处理)
36
2 (
3 @string NVARCHAR(4000)
4 )
5 AS
6 BEGIN
7 DECLARE @object_id nvarchar(500)
8 DECLARE @i INT
9 DECLARE @len INT
10 print @string
11 IF (@string IS NULL) OR (LTRIM(@string) = '')
12 RETURN
13
14 WHILE CHARINDEX(',',@string) > 0
15 BEGIN
16 SET @len = LEN(@string)
17 SET @i = CHARINDEX(',', @string)
18 SET @object_id = LEFT(@string, @i-1)
19 INSERT INTO a (id) VALUES (@object_id)--少做修改,改成需要的sql语句即可
20 SET @string = RIGHT(@string, @len - @i)
21 END
22 SET @object_id = @string
23 INSERT INTO a (id) VALUES (@object_id)--少做修改,改成需要的sql语句即可
24 END
25 go
26
27
28 --测试
29 create table a
30 (
31 id int
32 )
33 select * from a
34 exec sp_split_string '102,103,105,106,107,108,200,500,306,408'
35 select * from a 向存储过程中传数组字符串(并存储过程字符串的处理)
36