tasklist_deletetasklist.sql

来自「一个采用VS2008+Sql2000开发的任务管理系统」· SQL 代码 · 共 36 行

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

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

--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; --Invalid user

BEGIN TRANSACTION DeleteTaskList;

	DELETE FROM TaskListItems WHERE TaskListID = @TaskListID
	IF @@Error <> 0 BEGIN Rollback Transaction DeleteTaskList; END

	DELETE FROM TaskListAssignments WHERE TaskListID = @TaskListID
	IF @@Error <> 0 BEGIN Rollback Transaction DeleteTaskList; END

	DELETE FROM TaskListNames WHERE ID = @TaskListID
	IF @@Error <> 0 BEGIN Rollback Transaction DeleteTaskList; END

COMMIT TRANSACTION DeleteTaskList;