在写业务流系统的存储过程

CREATE   PROCEDURE  GetVertifiedWorkIdByWorkReader 
         @WorkReader   varchar ( 100 )
  AS

Declare   @PointerPrev   int
Declare   @PointerCurr   int

Declare   @temp   varchar ( 10 )
Set   @PointerPrev = 1
Set   @PointerCurr = 1

BEGIN   TRANSACTION
Set  NoCount  ON


Set   @PointerCurr = CharIndex ( ' , ' , @WorkReader , @PointerPrev + 1 )
set   @temp = cast ( SUBSTRING ( @WorkReader , @PointerPrev , @PointerCurr - @PointerPrev )  as   varchar )

CREATE   TABLE  # temp  (WorkId  varchar ( 6 ),
UserName  varchar  ( 250 ),BanCi  varchar  ( 10 ), [ Type of work ]    varchar  ( 10 ),DepartmentName  varchar  ( 50 ))
INSERT   INTO  # temp  
select  OperationUser.WorkId,OperationUser.UserName,BanCi.BanCi, gongzhong. [ Type of work ] ,OperationDepartment.DepartmentName  from  OperationUser,gongzhong,BanCi,OperationDepartment 
where  WorkId  in  ( @temp )
and  gongzhong.gongzhongId = OperationUser.gongzhongId 
and  BanCi.BanCiId = OperationUser.banciId 
and  OperationDepartment.DepartmentId = OperationUser.DepartmentId


SET   @PointerPrev   =   @PointerCurr


while  ( @PointerPrev + 1   <   LEN ( @WorkReader ))
Begin
Set   @PointerCurr = CharIndex ( ' , ' , @WorkReader , @PointerPrev + 1 )
if ( @PointerCurr > 0 )
Begin
set   @temp = cast ( SUBSTRING ( @WorkReader , @PointerPrev + 1 , @PointerCurr - @PointerPrev - 1 )  as   varchar )
INSERT   INTO  # temp  
select  OperationUser.WorkId,OperationUser.UserName,BanCi.BanCi, gongzhong. [ Type of work ] ,OperationDepartment.DepartmentName  from  OperationUser,gongzhong,BanCi,OperationDepartment 
where  WorkId  in  ( @temp )
and  gongzhong.gongzhongId = OperationUser.gongzhongId 
and  BanCi.BanCiId = OperationUser.banciId 
and  OperationDepartment.DepartmentId = OperationUser.DepartmentId

End
else
Break
End

set   @temp = cast ( SUBSTRING ( @WorkReader , @PointerPrev + 1 , LEN ( @WorkReader ) - @PointerPrev )  as   varchar )
INSERT   INTO  # temp  
select  OperationUser.WorkId,OperationUser.UserName,BanCi.BanCi, gongzhong. [ Type of work ] ,OperationDepartment.DepartmentName  from  OperationUser,gongzhong,BanCi,OperationDepartment 
where  WorkId  in  ( @temp )
and  gongzhong.gongzhongId = OperationUser.gongzhongId 
and  BanCi.BanCiId = OperationUser.banciId 
and  OperationDepartment.DepartmentId = OperationUser.DepartmentId

select   *   from  # temp
drop   table  # temp
Set  NoCount  OFF


IF   @@error   =   0
BEGIN
     COMMIT   TRANSACTION
           
END

ELSE
BEGIN
     ROLLBACK   TRANSACTION
          
END
GO