应该用SQL2000 OpenXML更简单，效率更高，代码更可读：
' u! Y* z1 \/ e$ H8 y { CREATE Procedure [dbo].[ProductListUpdateSpecialList]9 z8 e+ f2 m( r$ n
(
( o4 v' |2 e O @ProductId_Array NVARCHAR(2000),
4 k9 h: W- M$ ~% R. { @ModuleId INT7 s9 u) T4 E. J
)9 U4 ^7 Y1 H7 ]! ]4 A! Z* N
AS" \. n0 z2 t' a3 ~. @3 m7 r I1 n; u
delete from ProductListSpecial where - B9 h) j! z2 w* H, b5 {( _& Q
-- If empty, return
7 p3 i$ ?0 c6 `7 E( { IF (@ProductId_Array IS NULL OR LEN(LTRIM(RTRIM(@ProductId_Array))) = 0)# b: O1 h' q3 q( r. R) \
RETURN
# k. X1 C: ?7 v% c DECLARE @idoc int
" f4 w/ P3 g# X- M3 U. c; r EXEC sp_xml_preparedocument @idoc OUTPUT, @ProductId_Array
@. J! S# L. ~7 j7 {. P9 H) I Insert into ProductListSpecial (ModuleId,ProductId)
) `# R* G" C. S1 n# T' `9 ` Select$ [ A# q* ^" C8 j: a& p+ W; A
@ModuleId,C.[ProductId]2 H) v% D& A; d6 C3 n. i
FROM' I# J- [6 A' j: y! O4 Q
OPENXML(@idoc, ’/Products/Product’, 3)
" l# ^# L ~: Z7 G2 [) H with (ProductId int ) as C1 E' g/ j, M% b3 d
where
* r% k# o* q3 u! D% H$ c" E o: a C.[ProductId] is not null
' T& b5 e( }+ O EXEC sp_xml_removedocument @idoc |
发表于 2012-7-31 21:12:12
