tasklist_add.sql

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

-- =============================================
-- TaskList_Add
-- =============================================
-- First checks to see if the user specified is valid, then
-- adds a Task List Item to the TaskListItems table.
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'TaskList_Add' 
	   AND 	  type = 'P')
    DROP PROCEDURE TaskList_Add
GO

CREATE PROCEDURE TaskList_Add
	@Username VarChar(50),
	@PasswordHash VarChar(50),
	@TaskListID BigInt,
	@Subject VarChar(255),
	@Body	VarChar(5000),
	@CreatedDate DateTime,
	@ModifiedDate DateTime,
	@Status Int,
  @CategoryID 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;

INSERT INTO TaskListItems
	(TaskListID, OwnerUserID, Subject, Body, CreatedDate, ModifiedDate, Status, CategoryID)
VALUES
	(@TaskListID, @CurrentUserID, @Subject, @Body, @CreatedDate, @ModifiedDate, @Status, @CategoryID);

IF @@Error <> 0 RETURN 1;