tasklist_getuserinformation.sql

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

-- =============================================
-- 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