SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[sp_adm_ReSynchUserResCatAssign]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_adm_ReSynchUserResCatAssign]
GO

CREATE PROCEDURE sp_adm_ReSynchUserResCatAssign 	
	@UserID VARCHAR(8) OUTPUT
AS

DECLARE
@CategoryID int,
@TotalAssigned int,
@TotalCompleted int

DECLARE cur_ResCatAssign CURSOR FORWARD_ONLY FOR
    SELECT ResearchCategoryId FROM tbl_ResearchAssignments WHERE UserId = @UserId

OPEN cur_ResCatAssign

BEGIN TRAN
FETCH NEXT FROM cur_ResCatAssign INTO @CategoryID

WHILE (@@FETCH_STATUS=0)
	BEGIN

	SELECT ResearchID FROM tbl_Research
		WHERE AssignedToUserId = @UserID AND ResearchCategory = @CategoryID
	SET @TotalAssigned = @@ROWCOUNT

	SELECT ResearchID FROM tbl_Research
		WHERE AssignedToUserId = @UserID AND ResearchCategory = @CategoryID AND ResearchStatus = 'CLOSED'
	SET @TotalCompleted = @@ROWCOUNT

	UPDATE tbl_ResearchAssignments SET TotalAssigned = @TotalAssigned, TotalCompleted = @TotalCompleted
		WHERE CURRENT OF cur_ResCatAssign
	
	FETCH NEXT FROM cur_ResCatAssign INTO @CategoryID

	END

COMMIT TRAN

CLOSE cur_ResCatAssign
DEALLOCATE cur_ResCatAssign











GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

GRANT  EXECUTE  ON [dbo].[sp_adm_ReSynchUserResCatAssign]  TO [SALSA]
GO