tasklist_addusertotasklist.sql

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

-- =============================================
-- TaskList_AddUserToTaskList
-- =============================================
-- Adds a user to the TaskListAssignments table, granting them permission to see a task list
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'TaskList_AddUserToTaskList' 
	   AND 	  type = 'P')
    DROP PROCEDURE TaskList_AddUserToTaskList
GO

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

--First, check to 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;

--Make sure that this user isn't already in the TaskListAssignments table
SELECT @ExistingUserID = UserID FROM TaskListAssignments WHERE UserID = @UserID AND TaskListID = @TaskListID
IF (@ExistingUserID IS NOT NULL) Return 1; --User is already in

--Finally, add the user to the TaskListAssignments table
INSERT INTO TaskListAssignments (UserID, TaskListID) VALUES (@UserID, @TaskListID);
IF @@Error <> 0 Return 1;