CREATE PROCEDURE usp_IntRevApprove 

@SONum nvarchar(10),
@RevID int,
@reviewer char(10)

 AS

Declare @revtime datetime
SET @revtime = GETDATE()

Declare @revcount int
SET @revcount = (SELECT Count(SONUM) FROM OrderReviews WHERE SONum = @SONum AND RevID=@RevID)
SET @revcount = @revcount + 1

Declare @revreq int
SET @revreq = (SELECT reviewreq FROM Revisions WHERE SONum = @SONum AND RevID=@RevID)

Declare @reviewed int
SET @reviewed = (SELECT Count(SONum) FROM OrderReviews WHERE SONum = @SONum AND RevID=@RevID AND reviewer=@reviewer)

IF @reviewed  = 0

BEGIN
	IF @revcount >= @revreq

		BEGIN

		UPDATE Revisions 
		SET reviewcount = @revcount
		WHERE SONum = @SONum AND RevID = @RevID

		INSERT INTO OrderReviews (SONum, RevID, reviewer, accepted, revtime)
		VALUES (@SONum, @RevID, @reviewer, 'yes', @revtime)
		
		RETURN -1
		
		END
	ELSE
		BEGIN
		
		UPDATE Revisions 
		SET reviewcount = @revcount
		WHERE SONum = @SONum AND RevID = @RevID

		INSERT INTO OrderReviews (SONum, RevID, reviewer, accepted, revtime)
		VALUES (@SONum, @RevID, @reviewer, 'yes', @revtime)
		
		RETURN -2
		
		END
	END
ELSE
	BEGIN
		RETURN -3
	END
GO

quote:

---

Originally posted by visakh16

i've a doubt on below staement
SELECT reviewreq FROM Revisions WHERE SONum = @SONum AND RevID=@RevID

you're find out total count and number reviewed by reviewer seperately. Wht i think is shouldnt you be taking COUNT(reviewreq) in above statement to get number of articles yet to review rather than taking just reviewreq alone?

---