含参数数组的存储过程--openxml方式
1
CREATE Procedure [dbo].[ProductListUpdateSpecialList]
2(
3 @ProductId_Array NVARCHAR(2000),
4 @ModuleId INT
5)
6AS
7 delete from ProductListSpecial where ModuleId=@ModuleId
8
9 -- If empty, return
10 IF (@ProductId_Array IS NULL OR LEN(LTRIM(RTRIM(@ProductId_Array))) = 0)
11 RETURN
12
13 DECLARE @idoc int
14
15 EXEC sp_xml_preparedocument @idoc OUTPUT, @ProductId_Array
16
17 Insert into ProductListSpecial (ModuleId,ProductId)
18 Select
19 @ModuleId,C.[ProductId]
20 FROM
21 OPENXML(@idoc, '/Products/Product', 3)
22 with (ProductId int ) as C
23 where
24 C.[ProductId] is not null
25
26 EXEC sp_xml_removedocument @idoc
27
CREATE Procedure [dbo].[ProductListUpdateSpecialList] 2
( 3
@ProductId_Array NVARCHAR(2000), 4
@ModuleId INT 5
) 6
AS 7
delete from ProductListSpecial where ModuleId=@ModuleId 8
9
-- If empty, return 10
IF (@ProductId_Array IS NULL OR LEN(LTRIM(RTRIM(@ProductId_Array))) = 0) 11
RETURN 12
13
DECLARE @idoc int 14
15
EXEC sp_xml_preparedocument @idoc OUTPUT, @ProductId_Array 16
17
Insert into ProductListSpecial (ModuleId,ProductId) 18
Select 19
@ModuleId,C.[ProductId] 20
FROM 21
OPENXML(@idoc, '/Products/Product', 3) 22
with (ProductId int ) as C 23
where 24
C.[ProductId] is not null 25
26
EXEC sp_xml_removedocument @idoc 27
需要用到带有参数数组的存储过程，在网上看到这种操作xml方式的
KidYang
