TSQL技术问题：用Case语句处理行数未知的动态表及存储过程创建

Hey there! Since you mentioned you're having issues creating a stored procedure in SSMS 2017 with T-SQL, but didn't specify the exact problem you're facing, I'll cover a few common use cases based on your table structure and the fact that all tasks are currently assigned to StaffID='1'. These should give you a solid starting point:

1. Redistribute Tasks from StaffID='1' to Other Employees #

If you need to spread out the tasks assigned to StaffID='1' across other active staff members, this stored procedure will handle even distribution based on task creation date:

CREATE PROCEDURE dbo.RedistributeTasksFromStaff1
AS
BEGIN
    SET NOCOUNT ON; -- Suppress row count messages to keep output clean

    -- Capture all valid staff members except StaffID='1'
    DECLARE @AvailableStaff TABLE (StaffID VARCHAR(50), RowNumber INT);
    INSERT INTO @AvailableStaff (StaffID, RowNumber)
    SELECT StaffID, ROW_NUMBER() OVER (ORDER BY StaffID)
    FROM eStaff
    WHERE StaffID <> '1';

    -- Capture tasks assigned to StaffID='1' and assign a row number for distribution
    DECLARE @TasksToReassign TABLE (TaskID INT, RowNumber INT);
    INSERT INTO @TasksToReassign (TaskID, RowNumber)
    SELECT TaskID, ROW_NUMBER() OVER (ORDER BY CreateDate DESC)
    FROM eTasks
    WHERE StaffID = '1';

    -- Check if there are any staff to reassign tasks to
    DECLARE @TotalStaff INT = (SELECT COUNT(*) FROM @AvailableStaff);
    IF @TotalStaff = 0
    BEGIN
        RAISERROR('No other active staff members available to receive tasks', 16, 1);
        RETURN;
    END;

    -- Update task assignments using modulo to evenly distribute tasks
    UPDATE targetTasks
    SET targetTasks.StaffID = staff.StaffID
    FROM eTasks targetTasks
    JOIN @TasksToReassign taskRows ON targetTasks.TaskID = taskRows.TaskID
    JOIN @AvailableStaff staff ON (taskRows.RowNumber - 1) % @TotalStaff + 1 = staff.RowNumber;

    PRINT 'Task redistribution complete! Updated ' + CAST(@@ROWCOUNT AS VARCHAR(10)) + ' task records.';
END;

How it works: #

• Uses temporary tables to track available staff and tasks needing reassignment
• Uses row numbers and modulo arithmetic to ensure even distribution (no single staff member gets flooded with tasks)
• Prioritizes newer tasks (sorted by CreateDate DESC) for reassignment
• Includes error handling if there are no other staff to assign tasks to

2. Get Task Count Summary for All Staff #

If you need to generate a report showing how many tasks each staff member has (including StaffID='1'), this stored procedure returns total tasks and today's new tasks:

CREATE PROCEDURE dbo.GetStaffTaskSummary
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        s.StaffID,
        s.Name,
        COUNT(t.TaskID) AS TotalAssignedTasks,
        -- Count tasks created today
        COUNT(CASE WHEN CAST(t.CreateDate AS DATE) = CAST(GETDATE() AS DATE) THEN 1 END) AS TodayNewTasks
    FROM eStaff s
    LEFT JOIN eTasks t ON s.StaffID = t.StaffID
    GROUP BY s.StaffID, s.Name
    ORDER BY TotalAssignedTasks DESC;
END;

How it works: #

• Uses LEFT JOIN to ensure even staff with no tasks appear in the results
• Includes a conditional count to isolate tasks created on the current day
• Sorts results by total tasks to highlight the busiest staff first

3. Clean Up Old Tasks for StaffID='1' #

If you need to archive or delete old tasks assigned to StaffID='1', this stored procedure lets you specify a cutoff date:

CREATE PROCEDURE dbo.CleanupOldStaff1Tasks
    @CutoffDate DATE -- Tasks older than this date will be deleted
AS
BEGIN
    SET NOCOUNT ON;

    DELETE FROM eTasks
    WHERE StaffID = '1' AND CreateDate < @CutoffDate;

    PRINT 'Cleanup complete! Deleted ' + CAST(@@ROWCOUNT AS VARCHAR(10)) + ' old tasks for StaffID=''1''.';
END;

Example usage: #

-- Delete all tasks for StaffID='1' created before January 1st, 2024
EXEC dbo.CleanupOldStaff1Tasks '2024-01-01';

If your actual problem is different (like inserting tasks, handling cascading deletes, etc.), feel free to share more details and I can adjust the solutions!

内容的提问来源于stack exchange，提问作者Tscott