tasklist_addstatusshowpreference.sql

一个采用VS2008+Sql2000开发的任务管理系统

-- =============================================
-- TaskList_AddStatusShowPreference
-- =============================================
-- Adds a status id to the list of ids that are acceptable to show for this user
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'TaskList_AddStatusShowPreference' 
	   AND 	  type = 'P')
    DROP PROCEDURE TaskList_AddStatusShowPreference
GO

CREATE PROCEDURE TaskList_AddStatusShowPreference
	@Username VarChar(50),
  @PasswordHash VarChar(50),
  @StatusID BigInt
AS
DECLARE @CurrentUserID BigInt, @StatusCount BigInt
SET @CurrentUserID = null;
SET @StatusCount = 0;

--Check to make sure this is a valid user
SELECT @CurrentUserID = ID FROM TaskListUsers WHERE Username = @Username AND PasswordHash = @PasswordHash
IF (@CurrentUserID IS NULL) Return 1;

--Check to make sure this status isn't already on there
SELECT @StatusCount = Count(ID) FROM TaskListStatusShowPreferences 
WHERE UserID = @CurrentUserID AND StatusID = @StatusID
IF (@StatusCount > 0) Return 1;

--Insert the status item
INSERT INTO TaskListStatusShowPreferences (UserID, StatusID)
VALUES (@CurrentUserID, @StatusID)