tasklist_gettasklistitem.sql

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

-- =============================================
-- TaskList_GetTaskListItem
-- =============================================
-- Retrieves a task list item from the database
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'TaskList_GetTaskListItem' 
	   AND 	  type = 'P')
    DROP PROCEDURE TaskList_GetTaskListItem
GO

CREATE PROCEDURE TaskList_GetTaskListItem
	@Username VarChar(20),
	@PasswordHash VarChar(50),
	@TaskID BigInt,
	@TaskListID BigInt
AS
DECLARE @CurrentUserID BigInt
SET @CurrentUserID = NULL

--Make sure that this is a valid user, and that they have access to this task
SELECT @CurrentUserID=TaskListUsers.ID
FROM TaskListUsers 
LEFT JOIN TaskListAssignments AS Ta ON Ta.UserID = TaskListUsers.ID AND Ta.TaskListID = @TaskListID
WHERE Username = @Username AND PasswordHash = @PasswordHash AND Ta.ID IS NOT NULL;

IF (@CurrentUserID IS NULL)	RETURN 1; --Invalid user

SELECT TaskListItems.ID AS TaskID,
	OwnerUserID AS UserID,
	TaskListUsers.Username AS Username,
	CreatedDate AS CreatedOn,
	ModifiedDate AS ModifiedOn,
	Subject AS TaskSubject,
	Body AS TaskBody,
	Status AS TaskStatusValue,
	StatusDescription AS TaskStatusName,
  CategoryID AS CategoryID,
	TaskListID AS TaskListID
FROM TaskListItems
LEFT JOIN TaskListUsers ON TaskListUsers.ID = TaskListItems.OwnerUserID
LEFT JOIN TaskListStatus ON TaskListStatus.StatusValue = TaskListItems.Status
WHERE TaskListItems.ID = @TaskID