Cacher is the code snippet organizer for pro developers

ORDER BY
     CASE @sort_order
          WHEN 'ASC' THEN
               CASE @order_by
                    WHEN 'surname' THEN surname
                    WHEN 'forename' THEN forename
                    WHEN 'fullname' THEN fullname
                    ELSE surname
               END
          ELSE '1'
     END ASC,
     CASE @sort_order
          WHEN 'DESC' THEN
               CASE @order_by
                    WHEN 'surname' THEN surname
                    WHEN 'forename' THEN forename
                    WHEN 'fullname' THEN fullname
                    ELSE surname
               END
          ELSE '1'
     END DESC

SELECT  * FROM
		( 
			SELECT ROW_NUMBER() OVER ( ORDER BY id_grupo ) AS RowNum, * 
			FROM portal_proposta_rubrica_grupo			
		) AS result
		WHERE RowNum >= @reg_start AND RowNum <= @reg_end
		ORDER BY
			CASE @asc
				WHEN 'ASC' THEN
				   CASE @ord
						WHEN 'id_grupo' THEN id_grupo
				   END				   
				ELSE '1'
			END ASC,							  
			CASE @asc
				WHEN 'ASC' THEN
				   CASE @ord
						WHEN 'descricao' THEN descricao						
				   END 
				ELSE '1'
			END ASC,
			CASE @asc
				WHEN 'ASC' THEN
				    CASE @ord						
						WHEN 'rg_estado' THEN rg_estado
				   END		   
				ELSE '1'
			END ASC,
			CASE @asc
				WHEN 'DESC' THEN
				   CASE @ord
						WHEN 'id_grupo' THEN id_grupo
				   END				   
				ELSE '1'
			END DESC,							  
			CASE @asc
				WHEN 'DESC' THEN
				   CASE @ord
						WHEN 'descricao' THEN descricao						
				   END 
				ELSE '1'
			END DESC,
			CASE @asc
				WHEN 'DESC' THEN
				    CASE @ord						
						WHEN 'rg_estado' THEN rg_estado
				   END		   
				ELSE '1'
			END DESC;

pass in @OrderBy int, where positive is ASC, negative is DESC, actual number is the column to sort by

SELECT
    dt.yourColumn1
        ,dt.yourColumn2
        ,dt.yourColumn3
        ,CASE 
            WHEN @OrderBy>0 THEN dt.SortBy
            ELSE NULL
         END AS SortByAsc
        ,CASE 
            WHEN @OrderBy<0 THEN dt.SortBy
            ELSE NULL
         END AS SortByDesc
    FROM (SELECT
              yourColumn1
                  ,yourColumn2
                  ,yourColumn3
                  ,CASE
                      WHEN ABS(@OrderBy) = 1 THEN surname
                      WHEN ABS(@OrderBy) = 2 THEN forename
                      WHEN ABS(@OrderBy) = 3 THEN fullName
                      WHEN ABS(@OrderBy) = 4 THEN CONVERT(varchar(10),userId)
                      WHEN ABS(@OrderBy) = 5 THEN CONVERT(varchar(10),MobileNumber
                      WHEN ABS(@OrderBy) = 6 THEN DeviceStatus
                      WHEN ABS(@OrderBy) = 7 THEN LastPosition
                      WHEN ABS(@OrderBy) = 8 THEN CONVERT(varchar(23),LastAlert,121)
                      WHEN ABS(@OrderBy) = 9 THEN CONVERT(varchar(23),LastCommunication,121)
                      WHEN ABS(@OrderBy) =10 THEN CONVERT(varchar(23),LastPreAlert,121)
                      ELSE NULL
                  END AS SortBy
              FROM YourTablesHere
              WHERE X=Y
         ) dt
    ORDER BY SortByAsc ASC, SortByDesc DESC

just make sure you build string that sort properly, notice I used 'YYYY-MM-DD hh:mm:ss.mmm' for the dates and put the numbers into strings. We usually put multiple columns together, so if you sort by surname, forename is used too, etc. Watch out, if you do combine multiple columns you'll need to pad with zeros or spaces.

If you don't want the SortByAsc and SortByDesc columns to be in the result set, wrap the entire thing in a derived table.

If those aren't convertible to the same type, you may need to separate them into two separate clauses. The reason is that CASE is an expression that returns a value of a single data type, and the data type presented by all potential options must be convertible. If there's no ELSE then NULL is returned, so it will order all rows the same for that "branch":

 ORDER BY 
  CASE @Order
       WHEN 'Suite' THEN CTE.Suite END,
  CASE @Order
       WHEN 'Cost' THEN CTE.costSQFT END;

It may be possible that you could explicitly convert one of these, but that may change its meaning (e.g. you probably still want 11.0 to sort after 9.0, and converting both to a string won't do that.