View Code
CREATE
PROCEDURE
addusermodule
@ModuleId_Array
varChar
(
2000
),
@UserId
int
AS
DECLARE
@PointerPrev
int
DECLARE
@PointerCurr
int
DECLARE
@TId
int
Set
@PointerPrev
=
1
set
@PointerCurr
=
1
begin
transaction
Set
NoCount
ON
delete
from
tests
where
userid
=
@UserId
Set
@PointerCurr
=
CharIndex
(
'
,
'
,
@ModuleId_Array
,
@PointerPrev
+
1
)
set
@TId
=
cast
(
SUBSTRING
(
@ModuleId_Array
,
@PointerPrev
,
@PointerCurr
-
@PointerPrev
)
as
int
)
Insert
into
tests (userid,moduleid)
Values
(
@UserId
,
@TId
)
SET
@PointerPrev
=
@PointerCurr
while
(
@PointerPrev
+
1
<
LEN
(
@ModuleId_Array
))
Begin
Set
@PointerCurr
=
CharIndex
(
'
,
'
,
@ModuleId_Array
,
@PointerPrev
+
1
)
if
(
@PointerCurr
>
0
)
Begin
set
@TId
=
cast
(
SUBSTRING
(
@ModuleId_Array
,
@PointerPrev
+
1
,
@PointerCurr
-
@PointerPrev
-
1
)
as
int
)
Insert
into
tests (userid,moduleid)
Values
(
@UserId
,
@TId
)
SET
@PointerPrev
=
@PointerCurr
End
else
Break
End
set
@TId
=
cast
(
SUBSTRING
(
@ModuleId_Array
,
@PointerPrev
+
1
,
LEN
(
@ModuleId_Array
)
-
@PointerPrev
)
as
int
)
Insert
into
tests (userid,moduleid)
Values
(
@UserId
,
@TId
)
delete
from
tests
where
moduleid
=
0
Set
NoCount
OFF
if
@@error
=
0
begin
commit
transaction
end
else
begin
rollback
transaction
end
GO
编辑器加载中...
