stored procedure : to sort or not to sort : option

Question

...@Sort bit) 
AS                
     SELECT
..............

and I want to Order only if Sort = true

How can I realize it ?

Thank you.

Answer 1

You test the value of the @sort parameter and execute different queries accordingly.

Using IF:

IF (@sort = 0)
  SELECT...
ELSE
  SELECT... 
  ORDER BY ....

Using CASE:

SELECT ...
ORDER BY
  CASE
    WHEN @sort = 1 THEN xxx
    ELSE 1
  END

Answer 2

I didn't expect this to work I must admit, but it does. In this example, we sort by town if @sort is set to true, otherwise, we use some other default sort (that must be the same type as the sort column).

DECLARE @sort bit SET @sort = 0

SELECT [addressId]
      ,[customerId]
      ,[addressTypeId]
      ,[address1]
      ,[address2]
      ,[address3]
      ,[town]
      ,[county]
      ,[postcode]
      ,[countryCode]
FROM
    [dbo].[tblAddress]
ORDER BY
    CASE
        WHEN @sort = 1 THEN town
        ELSE 'A'
    END ASC

Edited as per comment suggestion.

Answer 3

Assuming you are using SQL Server (a big assumption) - This post nicely describes a method to so this using dynamic sql (which you almost certainly shouldn't be doing b/c sql server will be unable to optimize the procedur.

But regardless of a method to do this, why would you want to? The added complexity of conditionally sorting is almost surely less optimal the writing a good stored procedure (with proper indexes) that always returns sorted data. Why would you ever specifically need unsorted data (and even if you do need it unsorted, there is no guarantee that SQL won't sort it for you anyway since the returned rows are in a non-deterministic order if a sort isn't specified)?

Answer 4

...
ORDER BY
    CASE
        WHEN @sort = 1 THEN sortcolumn
        ELSE 1 --constant value with same type of "sortcolumn" eg '19000101' or 'a'
    END