有 2 个条件的 SQL 案例 [英] SQL Case with 2 conditions

问题描述

我有一个可以接受 2 个不同参数的存储过程.第一个参数将确定我要排序的列，第二个参数将确定它是 ASC 还是 DESC

I have a stored procedure that will accept 2 different parameters. The first parameter will determine which column I want to sort on, the second parameter will determine whether it is ASC or DESC

Create Procedure Some_SP
    @sortcolumn varchar(10)
    @sortorder varchar(10)
AS
    Select * from empTable
    Order by
         CASE @sortcolumn WHEN 'First_Name' THEN fname END,
         CASE @sortcolumn WHEN 'Last_Name' THEN lname END,
         CASE @sortcolumn WHEN 'ID' THEN empID END,
         CASE @sortorder WHEN 'ascending' THEN ASC END,
         CASE @sortorder WHEN 'descending' THEN DESC END

它给了我语法错误.如何修复它以便我的 CASE 语句中有 2 个条件?

It is giving me syntax error. How do I fix it so that I can have 2 conditions in my CASE statement?

推荐答案

以下内容将起作用:

Select * from empTable
Order by
CASE WHEN @sortcolumn = 'First_Name' AND @SortOrder = 'ascending' THEN fname END ASC,
CASE WHEN @sortcolumn = 'First_Name' AND @SortOrder = 'descending' THEN fname END DESC

等等...

为了避免手动输入这些 case 语句中的每一个，您可以编写一个生成器"脚本来创建它(如果表定义会更改，则特别好):

In order to avoid typing each of these case statements by hand, you could write a "generator" script that you use to create this (especially good if the table definition would change):

SELECT 
    'CASE WHEN @SortColumn = ''' + C.name + ''' AND @SortOrder = ''ascending'' THEN ' + C.name + ' END ASC,' + CHAR(13) + CHAR(10) +
    'CASE WHEN @SortColumn = ''' + C.name + ''' AND @SortOrder = ''descending'' THEN ' + C.name + ' END DESC,'
FROM sys.columns C 
WHERE C.object_id = object_id('[Schema].[Table]')

这篇关于有 2 个条件的 SQL 案例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！