tasklist_getuserinformation.sql

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

-- =============================================
-- TaskList_GetUserInformation
-- =============================================
-- Retrieves a user object from the database
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'TaskList_GetUserInformation' 
	   AND 	  type = 'P')
    DROP PROCEDURE TaskList_GetUserInformation
GO

CREATE PROCEDURE TaskList_GetUserInformation
	@Username VarChar(20),
	@UserID BigInt
AS
DECLARE @CurrentUserID BigInt, @IsAdministrator Bit
SET @CurrentUserID = NULL

--Make sure that CurrentUserID = UserID OR this is an Administrator user.
SELECT @CurrentUserID = ID, @IsAdministrator = IsAdministrator
FROM TaskListUsers 
WHERE Username = @Username

IF (@CurrentUserID IS NULL) RETURN 1; --Invalid user
IF (@CurrentUserID != @UserID AND @IsAdministrator = 0) Return 1; --Invalid user

SELECT ID AS UserID,
			 Username AS Username,
			 PasswordHash AS PasswordHash,
       IsManager AS IsManager,
       IsAdministrator AS IsAdministrator,
       StartupViewOption AS StartupViewOption,
       StartupViewArgument AS StartupViewArgument,
       ShouldNotify,
       NotifyPeriod,
       LastNotified
FROM TaskListUsers
WHERE ID = @UserID