tasklist_gettasklist.sql

来自「一个采用VS2008+Sql2000开发的任务管理系统」· SQL 代码 · 共 59 行

-- =============================================
-- TaskList_GetTaskList
-- =============================================
-- Retrieves a user's task list
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'TaskList_GetTaskList' 
	   AND 	  type = 'P')
    DROP PROCEDURE TaskList_GetTaskList
GO

CREATE PROCEDURE TaskList_GetTaskList
	@Username VarChar(50),
	@PasswordHash VarChar(50),
	@TaskListID BigInt
AS

DECLARE @CurrentUserID BigInt, @StartupViewOption VarChar(20)
SET @CurrentUserID = NULL

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

IF (@CurrentUserID IS NULL) Return 1;

--Now, record this task list as the last task list viewed
IF (@StartupViewOption = 'LastTaskList')
  EXEC TaskList_ChangeStartupTaskListID @Username, @PasswordHash, @TaskListID

--Finally, grab the task list
SELECT TaskListItems.ID, 
       TaskListID, 
       OwnerUserID,
       CreatedDate, 
       ModifiedDate as LastModifiedDate,
       TaskListUsers.Username AS LastModifiedBy, 
       Subject,
       Body,
       TaskListStatus.StatusDescription AS Status,
       TaskListCategories.ID AS CategoryID,
       TaskListCategories.Description AS CategoryDescription,
       TaskListCategories.IconUrl AS IconUrl,
       TaskListSortOrderPreferences.SortOrder AS SortOrder
FROM TaskListItems
LEFT JOIN TaskListStatus ON TaskListStatus.StatusValue = TaskListItems.Status
LEFT JOIN TaskListSortOrderPreferences ON TaskListSortOrderPreferences.UserID = @CurrentUserID 
  AND TaskListSortOrderPreferences.StatusID = TaskListStatus.ID
LEFT JOIN TaskListCategories ON TaskListCategories.ID = TaskListItems.CategoryID
LEFT JOIN TaskListUsers ON TaskListUsers.ID = TaskListItems.OwnerUserID
WHERE TaskListID = @TaskListID AND TaskListStatus.ID IN
(
  SELECT StatusID FROM TaskListStatusShowPreferences WHERE UserID = @CurrentUserID
)
ORDER BY TaskListSortOrderPreferences.SortOrder ASC, ModifiedDate DESC
IF @@Error <> 0 Return 1;