tasklist_getusertasklists.sql

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

-- =============================================
-- TaskList_GetUserTaskLists
-- =============================================
-- Retrieves a list of task lists that this user belongs to
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'TaskList_GetUserTaskLists' 
	   AND 	  type = 'P')
    DROP PROCEDURE TaskList_GetUserTaskLists
GO

CREATE PROCEDURE TaskList_GetUserTaskLists
	@Username VarChar(20),
	@PasswordHash VarChar(50),
	@UserToLookUpID BigInt
AS
DECLARE @CurrentUserID BigInt, @IsAdministrator Bit
SET @CurrentUserID = NULL

--Check to make sure this is a valid user, and that they are looking up their own 
--properties, or this user has Administrator priviledges
SELECT @CurrentUserID=ID, @IsAdministrator = IsAdministrator
FROM TaskListUsers 
WHERE Username = @Username AND PasswordHash = @PasswordHash;

IF (@CurrentUserID IS NULL) RETURN 1; --Invalid user
IF (@CurrentUserID != @UserToLookUpID AND @IsAdministrator = 0) Return 1; --Invalid user

	SELECT TaskListNames.ID AS TaskListID, 
				 TaskListNames.[Name] AS TaskListName
	FROM TaskListUsers
	LEFT JOIN TaskListAssignments ON TaskListAssignments.UserID = TaskListUsers.ID
	LEFT JOIN TaskListNames ON TaskListNames.ID = TaskListAssignments.TaskListID
	WHERE TaskListUsers.ID = @UserToLookUpID AND TaskListNames.ID IS NOT NULL AND TaskListNames.Name IS NOT NULL
  ORDER BY TaskListNames.[Name] ASC