tasklist_deleteuser.sql

来自「该项目管理系统可对项目的过程进行管理和控制」· SQL 代码 · 共 43 行

-- =============================================
-- TaskList_DeleteUser
-- =============================================
-- Deletes a user
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'TaskList_DeleteUser' 
	   AND 	  type = 'P')
    DROP PROCEDURE TaskList_DeleteUser
GO

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

--Check to make sure that this is a valid user, and that they have administrator priviledges
SELECT @CurrentUserID = ID FROM TaskListUsers 
WHERE Username = @Username AND PasswordHash = @PasswordHash AND IsAdministrator = 1;
IF (@CurrentUserID IS NULL) RETURN 1; --Invalid user

BEGIN TRANSACTION DeleteUser;

	DELETE FROM TaskListAssignments WHERE UserID = @UserIDToDelete;
	IF @@Error <> 0 BEGIN Rollback Transaction DeleteUser; Return 1; END

	DELETE FROM TaskListSortOrderPreferences WHERE UserID = @UserIDToDelete;
	IF @@Error <> 0 BEGIN Rollback Transaction DeleteUser; Return 1; END

	DELETE FROM TaskListStatusShowPreferences WHERE UserID = @UserIDToDelete;
	IF @@Error <> 0 BEGIN Rollback Transaction DeleteUser; Return 1; END

	DELETE FROM TaskListUsers WHERE ID = @UserIDToDelete;
	IF @@Error <> 0 BEGIN Rollback Transaction DeleteUser; Return 1; END

COMMIT TRANSACTION DeleteUser;