IF statement in ORDER BY Clause of a SELECT Statement in a SQL Server Stored Procedure

Question

I am trying to create a stored procedure that takes a bit parameter which if true orders by one column and if false orders by another column. How would I implement this?

Here is what I have so far

CREATE PROCEDURE [dbo].[CLICK10_GetCP]
    @switch AS BIT
AS
    BEGIN
     SELECT
      acct_nbr,
      acct_name
     FROM
      acct
     ORDER BY 

    END
GO

Answer 1

A crude way:

IF @switch = 1
  BEGIN
    SELECT
            acct_nbr,
            acct_name
    FROM
            acct
    ORDER BY acct_nbr
  END
ELSE
  BEGIN
    SELECT
            acct_nbr,
            acct_name
    FROM
            acct
    ORDER BY acct_name
  END

You should also be able to use CASE..WHEN I think:

SELECT
  acct_nbr,
  acct_name
FROM
  acct
ORDER BY
  CASE @switch
    WHEN 1 THEN acct_nbr
    WHEN 0 THEN acct_name
  END

Answer 2

I'm not really sure you can? I'd end up doing this:

CREATE PROCEDURE [dbo].[CLICK10_GetCP]
    @switch AS BIT
AS
    BEGIN
        if @switch
        begin
      SELECT
        acct_nbr,
        acct_name
      FROM
        acct
      ORDER BY 
        acct_nbr
     end
     else
     begin
      SELECT
        acct_nbr,
        acct_name
      FROM
        acct
      ORDER BY 
        acct_name
     end
    END
GO

Answer 3

    SELECT
            acct_nbr,
            acct_name
    FROM
            acct
    ORDER BY case @switch when 1 then acct_name else acct_nbr end

Answer 4

I thought you could change the parameter to an int, and sort by the column with that ordinal:

CREATE PROCEDURE [dbo].[CLICK10_GetCP]
    @ordinal AS INT
AS
    BEGIN
        SELECT
                acct_nbr,
                acct_name
        FROM
                acct
        ORDER BY @Ordinal

    END
GO

This throws an nice error (SQL2005):

Msg 1008, Level 16, State 1, Line 4 The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.

Googling around found this solution here (SQL 2005 and up):

CREATE PROCEDURE [dbo].[CLICK10_GetCP]
    @switch AS BIT
AS
    BEGIN
        SELECT
                acct_nbr,
                acct_name
        FROM
                acct
        ORDER BY  CASE
            WHEN @switch = 0 THEN (RANK() OVER (ORDER BY acct_nbr, acct_name))
            WHEN @switch = 1 THEN (RANK() OVER (ORDER BY acct_name, acct_nbr))
      END
GO

Answer 5

ORDER BY
   CASE WHEN @switch = 0 THEN Field1 END,
   CASE WHEN @Switch = 1 THEN Field2 END

Answer 6

The most succinct approach is:

CREATE PROCEDURE [dbo].[CLICK10_GetCP]
    @switch AS BIT
AS
    BEGIN
        SELECT
                acct_nbr,
                acct_name
        FROM
                acct
        ORDER BY 
                case @switch
                    when 1 then acct_nbr
                    when 0 then acct_name
                end

    END
GO

This may result in a poor execution plan for larger tables, or it may not work at all if acc-nbr is an int and acct-name is a varchar.

Wrapping two separate queries in IF / ELSE statements may perform noticeably better for larger datasets or be the only workable approach if the data types of the two columns are different.

Answer 7

One way is to build up the Query something like this:

CREATE PROCEDURE [dbo].[CLICK10_GetCP]
    @switch AS BIT
AS
DECLARE
@SQL nvarchar(max)


     SET @SQL N'SELECT
            acct_nbr,
            acct_name
    FROM
            acct
    ORDER BY '

if(@switch)
BEGIN
    @SQL = @SQL + ' acct_nbr '
END
ELSE
BEGIN 
    @SQL = @SQL + ' acct_Name '
END

Exec SP_ExecuteSQL @SQL