tasklist_adduser.sql

该项目管理系统可对项目的过程进行管理和控制

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

CREATE PROCEDURE TaskList_AddUser
	@Username VarChar(20),
	@PasswordHash VarChar(50),
	@NewUserName VarChar(20),
	@NewPasswordHash VarChar(50),
  @IsManager Bit,
  @IsAdministrator Bit
AS
DECLARE @CurrentUserID BigInt, @NewUserID BigInt
SET @CurrentUserID = NULL

SELECT @CurrentUserID = ID
FROM TaskListUsers 
WHERE Username = @Username AND PasswordHash = @PasswordHash AND IsAdministrator = 1;

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

Begin Transaction CreateUser;

INSERT INTO TaskListUsers (Username, PasswordHash, IsManager, IsAdministrator) 
VALUES (@NewUserName, @NewPasswordHash, @IsManager, @IsAdministrator);
IF @@Error <> 0 BEGIN Rollback Transaction CreateUser; Return 1; END
SET @NewUserID = @@Identity;

INSERT INTO TaskListSortOrderPreferences (UserID, StatusID, SortOrder)
SELECT @NewUserID, TaskListStatus.ID, TaskListStatus.DefaultSortOrder 
FROM TaskListStatus
IF @@Error <> 0 BEGIN Rollback Transaction CreateUser; Return 1; END

INSERT INTO TaskListStatusShowPreferences (UserID, StatusID)
SELECT UserID, StatusID 
FROM TaskListSortOrderPreferences 
WHERE UserID = @NewUserID  
IF @@Error <> 0 BEGIN Rollback Transaction CreateUser; Return 1; END

Commit Transaction CreateUser;