最近一直在做Dnn模块的开发,过程中碰到这么一个问题,需要同时插入N条数据,不想在程序里控制,但是SQL Sever又不支持数组参数.所以只能用变通的办法了.利用SQL Server强大的字符串处理传把数组格式化为类似"1,2,3,4,5,6"。
3 L) o# n& Y, w0 B 然后在存储过程中用SubString配合CharIndex把分割开来7 i9 T, e9 h5 m( V3 m# [- P
详细的存储过程
, x/ \( M9 D- T0 p8 Y% V( f4 T CREATE PROCEDURE dbo.ProductListUpdateSpecialList ?0 M. K/ v6 @, X; O& u
@ProductId_Array varChar(800),! s+ r* S' S+ c3 [0 G& O
@ModuleId int9 X" x! W# {2 j* Z5 s; i. J5 S
AS" G! s8 _/ d$ `2 R' |
DECLARE @PointerPrev int
; h; p0 Q# U( ~7 ]+ R2 E+ K DECLARE @PointerCurr int
6 l- B% s$ z5 {: S" L DECLARE @TId int, O( _# g$ E% ]: G
Set @PointerPrev=18 I |: @/ J. i( M5 c
set @PointerCurr=1
& U9 g9 |% r& `/ q/ j2 g begin transaction6 w6 d+ \: C- D: H q! S& i# M. f
Set NoCount ON) O6 i _% F V2 r0 |+ D
delete from ProductListSpecial where
3 { g3 i8 q Q- Z Set @PointerCurr=CharIndex(’,’,@ProductId_Array,@PointerPrev+1)
( E2 o7 A( k' X set @TId=cast(SUBSTRING(@ProductId_Array,@PointerPrev,@PointerCurr-@PointerPrev) as int)! N! N4 g! S9 A/ L4 Y. {& S
Insert into ProductListSpecial (ModuleId,ProductId) Values(@ModuleId,@TId)
3 \, A) E+ T3 Z/ | SET @PointerPrev = @PointerCurr2 I) _" h/ l2 P
while (@PointerPrev+1 < LEN(@ProductId_Array)) R W8 [2 B4 P$ y+ {2 y. z
Begin5 c, j5 |$ i) @- U. n
Set @PointerCurr=CharIndex(’,’,@ProductId_Array,@PointerPrev+1)
4 c) V1 c5 \: _4 e if(@PointerCurr>0)1 M0 X' T" Q X+ X* ]; {
Begin3 [5 I, I+ b4 @* D
set @TId=cast(SUBSTRING(@ProductId_Array,@PointerPrev+1,@PointerCurr-@PointerPrev-1) as int)
1 Y5 ]( \- Z! t) P% I N6 ?; a Insert into ProductListSpecial (ModuleId,ProductId) Values(@ModuleId,@TId)* T' X, G3 E" Z" a2 c: A5 o
SET @PointerPrev = @PointerCurr& F$ r/ x& z& V7 g
End
& Q* `( p! Y" ]3 ~ else
2 V/ Y: G9 q/ U Break
/ U' V/ ?' f8 W, Q0 K1 a End& B5 m: _9 Y( @# j: U
set @TId=cast(SUBSTRING(@ProductId_Array,@PointerPrev+1,LEN(@ProductId_Array)-@PointerPrev) as int)
% S9 \: @1 v9 O1 q1 D Insert into ProductListSpecial (ModuleId,ProductId) Values(@ModuleId,@TId)9 E: |8 Q& Q" J2 p3 t
Set NoCount OFF8 D. L) P% W3 t5 a) ?) h4 j N) P9 P
if @@error=0' k) n' n) _* V- [* P' {
begin( o+ }2 `8 F& Z) X% @$ s4 M9 }
commit transaction* K, m# G9 ~+ S) T
end
5 h3 ` M; z' m% }# v5 Z else
2 s1 K- R2 j" X+ X% O! _! S' V4 ]* w+ Z begin: A; w* I a- G1 z6 U
rollback transaction
3 D$ S) Y" n2 u/ ^1 l: C end, ^: n" t! b& D9 t. f$ W( x% J1 S
GO |