修改SQL存储过程ResourceLoading中Resources列的记录排序顺序

Got it, let's sort out this row order issue for your Resources column. The problem here is that when using UNION ALL without an explicit ORDER BY, SQL Server doesn't guarantee the order of your results—even though you defined the unions in a specific sequence, the execution plan might rearrange them based on internal optimizations.

Here's the adjusted stored procedure that enforces your desired order: UsedCapacity → AvailableCapacity → ResourceLoad:

ALTER PROCEDURE [dbo].[ResourceLoading] 
    @ResourceId varchar(40), 
    @Bucketdate varchar(40), 
    @EndBucketDate varchar(40) 
AS 
BEGIN 
    -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
    SET NOCOUNT ON; 

    Declare @Variable nvarchar(max)='', @ResourceTask nvarchar(max)='';

    select @Variable += QUOTENAME(BucketDate)+ ',' 
    from BucketMaster 
    where BucketDate <= @EndBucketDate and BucketDate >= @Bucketdate 
    Order by BucketDate;

    set @Variable = Left(@Variable,len(@Variable)-1);

    set @ResourceTask = '
        select Resources, ' + @Variable + '
        from (
            select 
                UsedCapacity as value,
                BucketDate,
                ResourceId,
                ''UsedCapacity'' As ''Resources'',
                1 as SortOrder -- Assign sort weight for UsedCapacity
            from BucketCapacity 
            inner join BucketMaster on BucketCapacity.BucketId=BucketMaster.BucketId 
            where ResourceId='''+@ResourceId+''' and BucketDate <= '''+@EndBucketDate+''' and BucketDate >= '''+@BucketDate+'''

            union all 

            select 
                AvailableCapacity as value,
                BucketDate,
                ResourceId,
                ''AvailableCapacity'' As ''Resources'',
                2 as SortOrder -- Assign sort weight for AvailableCapacity
            from BucketCapacity 
            inner join BucketMaster on BucketCapacity.BucketId=BucketMaster.BucketId 
            where ResourceId='''+@ResourceId+''' and BucketDate <= '''+@EndBucketDate+''' and BucketDate >= '''+@BucketDate+'''

            union all 

            select 
                (cast (round (UsedCapacity *1.00 / AvailableCapacity,3) as float )) as value,
                BucketDate,
                ResourceId,
                ''ResourceLoad'' As ''Resources'',
                3 as SortOrder -- Assign sort weight for ResourceLoad
            from BucketCapacity 
            inner join BucketMaster on BucketCapacity.BucketId=BucketMaster.BucketId 
            where ResourceId='''+@ResourceId+''' and BucketDate <= '''+@EndBucketDate+''' and BucketDate >= '''+@BucketDate+'''
        ) t 
        pivot( 
            sum(value) for BucketDate in ('+@Variable+')
        ) as pivot_table
        ORDER BY SortOrder; -- Enforce the desired row order';

    execute sp_executesql @ResourceTask;
END

Key Changes Explained: #

• Added a SortOrder column to each UNION ALL branch: We assign a numeric value (1, 2, 3) that maps directly to your desired sequence.
• Explicit ORDER BY SortOrder in the final query: This tells SQL Server exactly how to sort the rows in the result set, overriding any default execution plan ordering.
• Updated the outer select to explicitly list Resources and the dynamic bucket columns (instead of SELECT *), which makes the query more readable and avoids including the SortOrder column in your final output.

This will reliably return your Resources rows in the order you need: UsedCapacity, AvailableCapacity, ResourceLoad.

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