【SQL开源代码栏目提醒】:以下是网学会员为您推荐的SQL开源代码-TaskList_AddUserToTaskList.sql,希望本篇文章对您学习有所帮助。
-- =============================================
-- TaskList_AddUserToTaskList
-- =============================================
-- Adds a user to the TaskListAssignments table, granting them per
mission 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 Pass
wordHash = @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;