给SQL Server存储过程传送数组参数的变通办法【转】
方法一、利用SQL Server强大的字符串处理传把数组格式化为类似"1,2,3
然后在存储过程中用SubString配合CharIndex把
存储过程如下:
1
CREATE PROCEDURE dbo.ProductListUpdateSpecialList
2
@ProductId_Array varChar(800),
3
@ModuleId int
4
AS
5
DECLARE @PointerPrev int
6
DECLARE @PointerCurr int
7
DECLARE @TId int
8
Set @PointerPrev=1
9
set @PointerCurr=1
10
11
begin transaction
12
Set NoCount ON
13
delete from ProductListSpecial where ModuleId=@ModuleId
14
15
Set @PointerCurr=CharIndex (',',@ProductId_Array ,@PointerPrev+1)
16
set @TId=cast (SUBSTRING(@ProductId_Array,@PointerPrev ,@PointerCurr-@PointerPrev) as int)
17
Insert into ProductListSpecial (ModuleId,ProductId) Values(@ModuleId,@TId)
18
SET @PointerPrev = @PointerCurr
19
while (@PointerPrev+1 < LEN(@ProductId_Array))
20
Begin
21
Set @PointerCurr=CharIndex( ',',@ProductId_Array, @PointerPrev+1)
22
if(@PointerCurr> 0)
23
Begin
24
set @TId=cast (SUBSTRING(@ProductId_Array,@PointerPrev +1,@PointerCurr- @PointerPrev-1) as int)
25
Insert into ProductListSpecial (ModuleId,ProductId) Values (@ModuleId,@TId)
26
SET @PointerPrev = @PointerCurr
27
End
28
else
29
Break
30
End
31
32
set @TId=cast( SUBSTRING(@ProductId_Array,@PointerPrev+ 1,LEN(@ProductId_Array )-@PointerPrev) as int)
33
Insert into ProductListSpecial (ModuleId,ProductId) Values (@ModuleId,@TId)
34
Set NoCount OFF
35
if @@error= 0
36
begin
37
commit transaction
38
end
39
else
40
begin
41
rollback transaction
42
end
43
GO
44![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
10
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
11
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
12
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
13
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
14
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
15
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
16
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
17
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
18
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
19
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
20
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
21
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
22
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
23
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
24
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
25
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
26
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
27
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
28
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
29
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
30
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
31
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
32
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
33
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
34
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
35
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
36
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
37
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
38
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
39
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
40
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
41
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
42
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
43
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
44
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
方法二、应该用SQL2000 OpenXML更简单,效率更高,代码更可读。
存储过程如下:
1
CREATE Procedure [dbo].[ProductListUpdateSpecialList]
2
(
3
@ProductId_Array NVARCHAR(2000),
4
@ModuleId INT
5
)
6![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
AS
8![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
delete from ProductListSpecial where ModuleId=@ModuleId
10![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
11
-- If empty, return
12
IF (@ProductId_Array IS NULL OR LEN(LTRIM(RTRIM(@ProductId_Array))) = 0)
13
RETURN
14![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
15
DECLARE @idoc int
16![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
17
EXEC sp_xml_preparedocument @idoc OUTPUT, @ProductId_Array
18![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
19
Insert into ProductListSpecial (ModuleId,ProductId)
20
Select
21
@ModuleId,C.[ProductId]
22
FROM
23
OPENXML(@idoc, '/Products/Product', 3)
24
with (ProductId int ) as C
25
where
26
C.[ProductId] is not null
27![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
28
EXEC sp_xml_removedocument @idoc
29![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
10
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
11
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
12
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
13
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
14
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
15
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
16
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
17
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
18
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
19
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
20
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
21
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
22
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
23
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
24
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
25
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
26
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
27
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
28
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
29
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)