SQL Server 2016升级顾问问题

Question

SQL Server 2016升级顾问报告数据库兼容性问题： 规则：在90或更高版本的兼容模式下，ORDER BY子句中不允许使用常量表达式。

Version90 规则标题：常量表达式未在ORDER允许BY子句中90或更高版本兼容模式

建议：在更改数据库的兼容模式到90或更高版本，修改，在使用常量表达式语句ORDER BY子句使用列名称或列别名或表示名称或别名在选择列表中的位置的非负整数。

规则ID：Microsoft.Rules.Data.Upgrade.UR00326

这是造成错误的SQL，我无法弄清楚如何改变Order By语句满足升级顾问???

SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 

ALTER PROCEDURE [Utilities].[GetProcStats] (@order varchar(100) = 'use') 
AS 
BEGIN 
WITH GetQueryStats 
(plan_handle, 
    total_elapsed_time, 
    total_logical_reads, 
    total_logical_writes, 
    total_physical_reads) 
AS 
(SELECT qs.plan_handle, 
    SUM(qs.total_elapsed_time) AS total_elapsed_time, 
    SUM(qs.total_logical_reads) AS total_logical_reads, 
    SUM(qs.total_logical_writes) AS total_logical_writes, 
    SUM(qs.total_physical_reads) AS total_physical_reads 
    FROM sys.dm_exec_query_stats qs 
    GROUP BY qs.plan_handle) 
SELECT DB_NAME(st.dbid) AS database_name, 
     OBJECT_SCHEMA_NAME(st.objectid, st.dbid) AS schema_name, 
     OBJECT_NAME(st.objectid, st.dbid) AS proc_name, 
     SUM(cp.usecounts) AS use_counts, 
     SUM(cp.size_in_bytes) AS size_in_bytes, 
     SUM(qs.total_elapsed_time) AS total_elapsed_time, 
     CAST(SUM(qs.total_elapsed_time) AS decimal(38, 4))/SUM(cp.usecounts) AS avg_elapsed_time_per_use, 
     SUM(qs.total_logical_reads) AS total_logical_reads, 
     CAST(SUM(qs.total_logical_reads) AS decimal(38, 4))/SUM(cp.usecounts) AS avg_logical_reads_per_use, 
     SUM(qs.total_logical_writes) AS total_logical_writes, 
     CAST(SUM(qs.total_logical_writes) AS decimal(38, 4))/SUM(cp.usecounts) AS avg_logical_writes_per_use, 
     SUM(qs.total_physical_reads) AS total_physical_reads, 
     CAST(SUM(qs.total_physical_reads) AS decimal(38, 4))/SUM(cp.usecounts) AS avg_physical_reads_per_use, 
     st.text 
    FROM sys.dm_exec_cached_plans cp 
    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st 
    INNER JOIN GetQueryStats qs ON cp.plan_handle = qs.plan_handle 
    INNER JOIN sys.procedures p ON st.objectid = p.object_id 
    WHERE p.type IN ('P', 'PC') 
    GROUP BY st.dbid, st.objectid, st.text 
    ORDER BY 
    CASE @order 
     WHEN 'name' THEN OBJECT_NAME(st.objectid) 
     WHEN 'size' THEN SUM(cp.size_in_bytes) 
     WHEN 'read' THEN SUM(qs.total_logical_reads) 
     WHEN 'write' THEN SUM(qs.total_logical_writes) 
    ELSE SUM(cp.usecounts) 
END DESC; 
END; 

我试着根据建议将ORDER BY更改为以下内容。

ORDER BY CASE WHEN = @order '姓名' THEN OBJECT_NAME（st.objectid，st.dbid） WHEN @order = '尺寸' THEN SUM（cp.size_in_bytes） WHEN @order = '读' THEN SUM（qs.total_logical_reads） WHEN @order ='write'THEN SUM（qs.total_logical_writes） ELSE SUM（cp.usecounts） END DESC;

但是，SQL Server 2016升级顾问仍报告相同的问题。

Answer 1

您不能在Order By子句中使用变量。

Sql Server 2008: Strange error in stored procedure

有一个可能的解决方案