ORDER BY  (CASE WHEN @aint_OrderingId = 1 THEN Class_Code ELSE 1 END) ASC,
		  (CASE WHEN @aint_OrderingId = 1 THEN Meal_Type ELSE 1 END) ASC,
		  (CASE WHEN @aint_OrderingId = 1 THEN Particular ELSE 1 END) ASC,
		  (CASE WHEN @aint_OrderingId = 2 THEN MTRR.Meal_Type ELSE 1 END) ASC,  
		  (CASE WHEN @aint_OrderingId = 2 THEN MTRR.Class_Code ELSE 1 END) ASC,
		  (CASE WHEN @aint_OrderingId = 2 THEN MTRR.Format ELSE 1 END)ASC,
		  (CASE WHEN @aint_OrderingId = 3 THEN MTRR.Meal_Type  ELSE 1 END) ASC,
		  (CASE WHEN @aint_OrderingId = 3 THEN MTRR.Format ELSE 1 END) ASC,
		  (CASE WHEN @aint_OrderingId = 3 THEN MTRR.Class_Code ELSE 1 END) ASC,
		  (CASE WHEN @aint_OrderingId NOT IN (1, 2, 3) THEN M.Meal_Category ELSE 1 END) ASC,
		  (CASE WHEN @aint_OrderingId NOT IN (1, 2, 3) THEN MTRR.sort_Order ELSE 1 END) ASC;


ORDER BY 
(CASE WHEN @aint_OrderingId = 1 THEN Class_Code     + Meal_Type       + Particular      ELSE 1 END),
(CASE WHEN @aint_OrderingId = 2 THEN MTRR.Meal_Type + MTRR.Class_Code + MTRR.Format     ELSE 1 END),
(CASE WHEN @aint_OrderingId = 3 THEN MTRR.Meal_Type + MTRR.Format     + MTRR.Class_Code ELSE 1 END),
(CASE WHEN @aint_OrderingId NOT IN (1, 2, 3) THEN M.Meal_Category ELSE 1 END),
(CASE WHEN @aint_OrderingId NOT IN (1, 2, 3) THEN MTRR.sort_Order ELSE 1 END);

quote:

---

Originally posted by aoriju

Dear ms65g,

It's working for Same sort of data Types
WHEN @aint_OrderingId =2
THEN MTRR.Meal_Type + MTRR.Class_Code + MTRR.Format
WHEN @aint_OrderingId = 3
THEN MTRR.Meal_Type + MTRR.Format + MTRR.Class_Code
ELSE M.Meal_Category + MTRR.sort_Order

In my case else part is not working because....M.Meal_Category is Varchar and MTRR.sort_Order is numeric
If i cast this sort order as varchar then ordering will lost(That means 1,11.00,2...work like that.....

Any solution please reply back

---