tasklist_delete.sql

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

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

CREATE PROCEDURE TaskList_Delete
	@UserName VarChar(20),
	@PasswordHash VarChar(50),
	@TaskListID BigInt,
	@ID Bigint
AS
DECLARE @CurrentUserID BigInt
SET @CurrentUserID = NULL

--Check to make sure this is a valid user, and that they have access to this task list
SELECT @CurrentUserID=TaskListUsers.ID
FROM TaskListUsers 
LEFT JOIN TaskListAssignments ON TaskListAssignments.UserID = TaskListUsers.ID
  AND TaskListAssignments.TaskListID = @TaskListID
WHERE Username = @Username AND PasswordHash = @PasswordHash AND TaskListAssignments.ID IS NOT NULL;

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

DELETE FROM TaskListItems WHERE ID = @ID AND TaskListID = @TaskListID;
IF @@Error <> 0 RETURN 1;