tasklist_getusersassignedtotasklist.sql

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

-- =============================================
-- TaskList_GetUsersAssignedToTaskList
-- =============================================
-- Retrieves a list of users who are assigned to a specific task list
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'TaskList_GetUsersAssignedToTaskList' 
	   AND 	  type = 'P')
    DROP PROCEDURE TaskList_GetUsersAssignedToTaskList
GO

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

--Make sure this is a valid user and that they have manager priviledges
SELECT @CurrentUserID=ID
FROM TaskListUsers 
WHERE Username = @Username AND PasswordHash = @PasswordHash AND IsManager = 1;

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

SELECT 	TaskListUsers.ID as UserID,
				TaskListUsers.Username as Username
FROM TaskListAssignments
LEFT JOIN TaskListUsers ON TaskListUsers.ID = TaskListAssignments.UserID
WHERE TaskListAssignments.TaskListID = @TaskListID