tasklist_getchangedtasksfromtasklist.sql

该项目管理系统可对项目的过程进行管理和控制

-- =============================================
-- TaskList_GetChangedTasksFromTaskList
-- =============================================
-- creating the store procedure
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'TaskList_GetChangedTasksFromTaskList' 
	   AND 	  type = 'P')
    DROP PROCEDURE TaskList_GetChangedTasksFromTaskList
GO

CREATE PROCEDURE TaskList_GetChangedTasksFromTaskList
	@Username VarChar(20),
  @PasswordHash VarChar(50),
  @TaskListID BigInt
AS
DECLARE @UserID BigInt
DECLARE @LastNotified DateTime

SELECT @UserID = ID, @LastNotified = LastNotified FROM TaskListUsers
WHERE Username = @Username AND PasswordHash = @PasswordHash

-- Invalid User
IF @UserID IS NULL OR @UserID = 0
  Return(-1);

SELECT @UserID = UserID FROM TaskListAssignments WHERE
UserID = @UserID AND TaskListID = @TaskListID

-- User doesn't have access to this task list
IF @UserID IS NULL OR @UserID = 0
  Return(-2);

SELECT Username, Subject, ModifiedDate 
FROM TaskListItems
LEFT JOIN TaskListUsers ON TaskListUsers.ID = TaskListItems.OwnerUserID
WHERE TaskListID = @TaskListID AND ModifiedDate > @LastNotified;