按日期时间动态排序

Question

我对代码进行了如下修改：

declare @filterBy as nvarchar(255)  = 'C1'
declare @order as nvarchar(255)  = 'asc'
declare @globOrder as nvarchar(255) = 'CONVERT(DateTime, C3,101) ASC'


SELECT TOP (7000)

        [Project1].[IDC_IDCONTACT] AS [IDC_IDCONTACT], 
        [Project1].[C1] AS [C1], 
        [Project1].[C2] AS [C2], 
        [Project1].[ICP_PRENOM] AS [ICP_PRENOM], 
        [Project1].[IDC_NOSOC] AS [IDC_NOSOC], 
        [Project1].[C3] AS [C3], 
        [Project1].[C4] AS [C4], 
        [Project1].[C5] AS [C5], 
        [Project1].[ADC_CDPOSTAL] AS [ADC_CDPOSTAL], 
        [Project1].[ADC_VILLE] AS [ADC_VILLE], 
        [Project1].[Libelle] AS [Libelle], 
        [Project1].[Libelle1] AS [Libelle1]
        FROM ( SELECT [Project1].[IDC_IDCONTACT] AS [IDC_IDCONTACT], [Project1].[IDC_NOSOC] AS [IDC_NOSOC], [Project1].[ICP_PRENOM] AS [ICP_PRENOM], [Project1].[ADC_CDPOSTAL] AS [ADC_CDPOSTAL], [Project1].[ADC_VILLE] AS [ADC_VILLE], [Project1].[Libelle] AS [Libelle], [Project1].[Libelle1] AS [Libelle1], [Project1].[C1] AS [C1], [Project1].[C2] AS [C2], [Project1].[C3] AS [C3], [Project1].[C4] AS [C4], [Project1].[C5] AS [C5], row_number() OVER (ORDER BY CASE when @filterBy = 'DateNaissance' and @order = 'asc'  THEN CONVERT(DateTime, [Project1].C3,101) END asc , CASE when @filterBy = 'DateNaissance' and @order = 'desc'  THEN CONVERT(DateTime, [Project1].C3,101) END desc,CASE when @filterBy <> 'DateNaissance' and @order = 'asc'  THEN @filterBy END ASC , CASE when @filterBy <> 'DateNaissance' and @order = 'desc'  THEN @filterBy END desc) AS [row_number] , [Project1].profil AS prof
            FROM ( SELECT 
                [Extent1].[IDC_IDCONTACT] AS [IDC_IDCONTACT], 
                [Extent1].[IDC_NOSOC] AS [IDC_NOSOC], 
                [Extent2].[ICP_PRENOM] AS [ICP_PRENOM], 
                [Extent4].[ADC_CDPOSTAL] AS [ADC_CDPOSTAL], 
                [Extent4].[ADC_VILLE] AS [ADC_VILLE], 
                [Extent5].[Libelle] AS [Libelle], 
                [Extent6].[Libelle] AS [Libelle1], 
                [Extent1].[IDC_NOM] AS [C1], 
                [Extent2].[ICP_NMNAISS] AS [C2], 
                [Extent1].[IDC_CDPROFIL_CONTACT]  AS profil,
                 CAST( [Extent2].[ICP_DTNAISS] AS datetime2) AS [C3], 
                 CAST( [Extent3].[ICR_DTCREA_ENTREPRISE] AS datetime2) AS [C4], 
                CASE WHEN ([Extent1].[IDC_CDPORTEFEUILLE] IS NULL) THEN N'' ELSE  CAST( [Extent1].[IDC_CDPORTEFEUILLE] AS nvarchar(max)) END AS [C5]
                FROM      [mdw].[IDENTITE_CONTACT] AS [Extent1]
                LEFT OUTER JOIN [mdw].[IDENTITE_CONTACT_PHYSIQUE] AS [Extent2] ON [Extent1].[IDC_IDCONTACT] = [Extent2].[ICP_IDCONTACT] 
                LEFT OUTER JOIN [mdw].[IDENTITE_CONTACT_PROFESSIONNEL] AS [Extent3] ON [Extent1].[IDC_IDCONTACT] = [Extent3].[ICR_IDCONTACT]
                LEFT OUTER JOIN [mdw].[ADRESSE_CONTACT] AS [Extent4] ON [Extent1].[IDC_IDCONTACT] = [Extent4].[ADC_IDADR]
                LEFT OUTER JOIN [mdw].[Ref_Profil_Contact] AS [Extent5] ON [Extent1].[IDC_CDPROFIL_CONTACT] = [Extent5].[Cod]
                LEFT OUTER JOIN [mdw].[Ref_Statut] AS [Extent6] ON [Extent1].[IDC_CDSTATUTSOC] = [Extent6].[Cod]  WHERE IDC_CDPORTEFEUILLE IN (0108,0208,1808))  AS [Project1]
        )  AS [Project1]WHERE [Project1].[row_number]  > 0 

当@filterBy具有值'DateNaissance‘时，它可以工作，但当它接受另一个值时，它就不工作了，问题是我不能传递一个变量来排序依据

Answer 1

有两种情况是可能的：

ORDER BY
        CASE UPPER(@order)
        WHEN 'ASC'
        THEN CONVERT(DateTime, C3,101)
        END ASC,
        CASE UPPER(@order)
        WHEN 'DESC'
        THEN CONVERT(DateTime, C3,101)
        END DESC

这里已经有人回答了：DESCENDING/ASCENDING Parameter to a stored procedure

Answer 2

您不能在order by中使用声明var。如果愿意，您可以使用CASE语句，如下所示：

ORDER BY
    CASE  
        WHEN @order = 'asc'
        THEN CONVERT(DateTime, C3,101) ASC
        WHEN @order = 'desc'
        THEN CONVERT(DateTime, C3,101)  DESC
    END

Answer 3

只需使用以下代码更改您的row_number逻辑行：

将@order声明为nvarchar(255) = 'desc‘

     declare @filterBy as nvarchar(255)  = 'C3'

    select row_number() OVER (ORDER BY CASE @filterBy WHEN 'DateNaissance' THEN CONVERT(DateTime, col1,101) + @order END  , @filterBy DESC) AS [row_number] ,col1
    from
    (select cast(GETDATE()AS datetime2) AS col1
    union all
    select cast(GETDATE()- 2 AS datetime2) AS col1) base

您正在使用@order by after end of case，这是通过错误在case before中使用order by的正确方法