22,210
社区成员
发帖
与我相关
我的任务
分享
Create PROCEDURE [dbo].[sp_AssignPaper]
@CURRENTUSER VARCHAR(20)
AS
BEGIN
DECLARE @I INT
DECLARE @MAX INT
DECLARE @CT INT
DECLARE @PageLowerBound INT
DECLARE @PageUpperBound INT
DECLARE @username VARCHAR(20)
DECLARE @PcmemberList TABLE(id int IDENTITY(1, 1),USERNAME VARCHAR(20))
insert into @PcmemberList
select UserName from AUDIT_USER where roletype=3
and domainid=(select top 1 domainid from AUDIT_USER where username=@CURRENTUSER)
SET @I=1
SELECT @MAX = MAX(ID) FROM @PcmemberList
select @CT=count(1) from UPLOADFILE where TRACKCHAIRUSER=@CURRENTUSER
AND PCMemberUser IS NULL and PAPERSTATUS=1
set @PageLowerBound=0
set @PageUpperBound=@PageLowerBound+ROUND(@CT/@MAX,0)
create table #pageindex(id int identity(1,1) not null,nid int)
set rowcount @PageUpperBound
WHILE (@I<=@MAX)
BEGIN
IF EXISTS (SELECT 1 FROM @PcmemberList WHERE ID = @I)
BEGIN
INSERT INTO #pageindex(nid) select fid from UPLOADFILE
SELECT @username=UserName FROM @PcmemberList WHERE id = @I
UPDATE UPLOADFILE SET PCMemberUser=@username
FROM UPLOADFILE F INNER JOIN #pageindex P ON F.FID=P.NID
WHERE F.PAPERSTATUS=2 AND F.TRACKCHAIRUSER=@CURRENTUSER AND F.PCMemberUser IS NULL
AND P.ID>@PageLowerBound AND P.ID<=@PageUpperBound
SET @I = @I+1
END
END
drop table #pageindex
END