【SQL开源代码栏目提醒】:网学会员为广大网友收集整理了,TaskList_GetUsersAssignedToTaskList.sql,希望对大家有所帮助!
-- =============================================
-- TaskList_GetUsersAssignedToTaskList
-- =============================================
-- Retrieves a list of users who are assigned to a specific task list
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'TaskList_GetUsersAssignedToTaskList'
AND type = 'P')
DROP PROCEDURE TaskList_GetUsersAssignedToTaskList
GO
CREATE PROCEDURE TaskList_GetUsersAssignedToTaskList
@Username VarChar(20),
@PasswordHash VarChar(50),
@TaskListID BigInt
AS
DECLARE @CurrentUserID BigInt
SET @CurrentUserID = NULL
--Make sure this is a valid user and that they have manager priviledges
SELECT @CurrentUserID=ID
FROM TaskListUsers
WHERE Username = @Username AND Pass
wordHash = @PasswordHash AND IsManager = 1;
IF (@CurrentUserID IS NULL) RETURN 1; --Invalid user
SELECT TaskListUsers.ID as UserID,
TaskListUsers.Username as Username
FROM TaskListAssignments
LEFT JOIN TaskListUsers ON TaskListUsers.ID = TaskListAssignments.UserID
WHERE TaskListAssignments.TaskListID = @TaskListID