0

i modified the code like below:

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

it work when @filterBy have the value 'DateNaissance' but it's not working when it takes another value , the probleme is i can't pass a variable to order by

Improve this question
6
  • Which is your DBMS? (SQL Server, Oracle, MySql) Commented Aug 6, 2015 at 7:53
  • i'm trying to execute it from SSMS Commented Aug 6, 2015 at 8:05
  • Please put break points in your query and try to debug the procedure. Commented Aug 6, 2015 at 8:05
  • Look at this link Commented Aug 6, 2015 at 8:12
  • Add @order inside case statement. Commented Aug 6, 2015 at 8:13

3 Answers 3

Reset to default
1

It's possible with two CASEs:

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

It was answered here already: DESCENDING/ASCENDING Parameter to a stored procedure

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

You can't use declare var in your order by. You can use CASE statement if you want, something like this: 

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

5 Comments

He can use alias, you probably mean he can not use variable as he use it.
Aliases can be used, but not a variable for ASC or DESC.
I comment the text "...You can't use alias in your order by..." - this is not true @Vojtěch Dohnal.
how i can use alias ?
like you can see in the edited version of my question , i tried this solution , it work for datetim because i mention the name of colone(C3) but not for the other , because i pass a variable (filterBy)
0

Just change you row_number logic line with below code:

declare @order as 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

You are using @order by after end of case which is through error the correct way to use order by in the case before End

Improve this answer

2 Comments

this is not working , i get the same error : can't convert datetime to string
i try with create logic which worked i will edit the post and send you the logic if you change the logic as your it will give same error.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.