How to use if else inside select statement-CodePudding

I have a stored procedure given below and i want to use if else inside select query

ALTER PROCEDURE updatepriority
    @sort smallint
AS 
BEGIN
    SELECT    
        Request.SubmitTime, Request.CommitTime, 
        (CASE WHEN Priority.Priority = 'Live Demo' THEN 1 WHEN Priority.Priority = 'Priority Re-do' THEN 2 ELSE 3 END) AS Sort1,
END

When @sort = 1 I want to use another case statement and I do not want to include the whole statement with if else

Something like below

IF (@sort = 1)
BEGIN
    (CASE WHEN Priority.Priority = 'Live Demo' THEN 1 WHEN Priority.Priority = 'Priority Re-do' THEN 2 ELSE 3 END) AS Sort1,
END
ELSE
BEGIN
    (CASE WHEN Priority.Priority = 'Live Demo' THEN 1 WHEN Priority.Priority = 'Conditional Live' THEN 2 WHEN Priority.Priority = 'Priority Re-do' THEN 3 ELSE 4 END) AS Sort1,
END

But the above query throws an error as we cannot use if else inside select statement

CodePudding user response：

yes, there's no else in sql server, but you can have this instead.

if(@sort=1)

BEGIN
    SELECT    Request.SubmitTime, Request.CommitTime, 
          (CASE WHEN Priority.Priority = 'Live Demo' THEN 1 WHEN Priority.Priority = 'Priority Re-do' THEN 2 ELSE 3 END) AS Sort1 ....
END

if(@sort!=1)
BEGIN
    SELECT    Request.SubmitTime, Request.CommitTime, 
          (CASE WHEN Priority.Priority = 'Live Demo' THEN 1 
            WHEN Priority.Priority = 'Conditional Live' THEN 2 
            WHEN Priority.Priority = 'Priority Re-do' THEN 3 ELSE 4 END) AS Sort1,
 ....
END

CodePudding user response：

If you really want to not use the select statement in both if/else branches, then the only option is to use dynamic SQL for the CASE assembly, as below:

create table #test (
    Priority varchar(30)
);

insert #test (Priority)
values 
    ('Live Demo'), 
    ('Priority Re-do'), 
    ('Conditional Live'), 
    ('ABC');

declare @sort smallint = 1;
declare @sqlStmt varchar(max);

if(@sort=1)
BEGIN
    set @sqlStmt = '
    (CASE 
        WHEN Priority.Priority = ''Live Demo'' THEN 1 
        WHEN Priority.Priority = ''Priority Re-do'' THEN 2 
        ELSE 3 END) AS Sort1';

END
ELSE
BEGIN
    set @sqlStmt = '
    (CASE 
        WHEN Priority.Priority = ''Live Demo'' THEN 1 
        WHEN Priority.Priority = ''Conditional Live'' THEN 2 
        WHEN Priority.Priority = ''Priority Re-do'' THEN 3 
        ELSE 4 END) AS Sort1';
END

set @sqlStmt = 'select Priority.Priority, ''SubmitTime'' as SubmitTime, '   @sqlStmt   ' from #test as Priority order by Sort1';

exec (@sqlStmt);

Of course, you can take only you need from this (the sql dynamic part..).

CodePudding user response：

You just need to nest CASE to make a bit of boolean logic

SELECT    
  Request.SubmitTime,
  Request.CommitTime,
  CASE WHEN @sort = 1 THEN
      CASE WHEN Priority.Priority = 'Live Demo' THEN 1
           WHEN Priority.Priority = 'Priority Re-do' THEN 2
           ELSE 3
      END
  ELSE
      CASE WHEN Priority.Priority = 'Live Demo' THEN 1
           WHEN Priority.Priority = 'Conditional Live' THEN 2
           WHEN Priority.Priority = 'Priority Re-do' THEN 3
           ELSE 4
      END
  END AS Sort1,
....

You can even combine the two branches to make it more concise, but the above method is the most straightforward