SQL Server - nested `IIF` generator for `GREATEST` & `LEAST` workaround

make-nested-iif.sql

drop table if exists
    #row_comps,
    #main,
    #mid,
    #end;

declare @indent varchar(50) = '    ';

-- manually input table & column names below
with cols as (
  select 
    row_number() over (order by c.[column_id] asc) as ordinal,
    c.[name] -- quotename() as needed here
  from sys.columns as c
  where c.[object_id] = object_id('dbo.foo') -- update table name here
    and c.[name] in (                        -- hard code column names here
        'a',
        'b',
        'c',
        'd',
        'e',
        'f',
        'g',
        'h'
    ) 
), row_comps as (
  select 
    ref.ordinal as ref_ordinal,
    dif.ordinal as dif_ordinal,
    ref.[name] as ref_name,
    dif.[name] as dif_name,
    concat(
      ref.[name],
      ' >= ',
      'isnull(',dif.[name],',',ref.[name],')'
    ) as gt,
    concat(
      ref.[name],
      ' <= ',
      'isnull(',dif.[name],',',ref.[name],')'
    ) as lt
  from cols as ref
  outer apply cols as dif
  where ref.[name] <> dif.[name]
   and ref.ordinal < dif.ordinal
)
select * 
into #row_comps
from row_comps;

-- select * from #row_comps order by 1, 2

declare @else_column sysname;

select top(1)
    @else_column = rc.dif_name
from #row_comps as rc
where rc.dif_ordinal = (select max(dif_ordinal) from #row_comps);

-- print @else_column

select 
  1 as body_ordinal,
  ref_ordinal,
  concat(
    replicate(@indent, ref_ordinal),
    'iif('+char(10),
    replicate(@indent, ref_ordinal + 1)+'('+char(10),
    string_agg(
      replicate(@indent, ref_ordinal + 2) + rc.gt,
      ' and '+char(10)
    ),
    char(10),
    replicate(@indent, ref_ordinal + 1)+'),'+char(10),
    replicate(@indent, ref_ordinal + 1 )+rc.ref_name+','+char(10)
  ) as gt,
  concat(
    replicate(@indent, ref_ordinal),
    'iif('+char(10),
    replicate(@indent, ref_ordinal + 1)+'('+char(10),
    string_agg(
      replicate(@indent, ref_ordinal + 2) + rc.lt,
      ' and '+char(10)
    ),
    char(10),
    replicate(@indent, ref_ordinal + 1)+'),'+char(10),
    replicate(@indent, ref_ordinal + 1 )+rc.ref_name+','+char(10)
  ) as lt
into #main  
from #row_comps as rc
group by ref_ordinal, ref_name
order by ref_ordinal asc;

select 
  2 as body_ordinal,
  max(ref_ordinal) as ref_ordinal,
  replicate(@indent,max(ref_ordinal)+1)+@else_column+char(10) as [iif]
into #mid
from #row_comps as rc;

select 
  3 as body_ordinal,
  ref_ordinal,
  concat(
    replicate(@indent,ref_ordinal+1)+')',
    iif(
      ref_ordinal=1,
      ' as col_name',
      char(10)
    )
  ) as [iif]
into #end
from (
  select distinct ref_ordinal
  from #row_comps 
) as rc
order by ref_ordinal desc;

-- greatest
declare 
  @greatest nvarchar(max) = N'',
  @least    nvarchar(max) = N'';

select 
  @greatest += gt,
  @least    += lt
from #main
order by ref_ordinal;

select 
  @greatest += [iif],
  @least    += [iif]
from #mid

select 
  @greatest += replace([iif],'col_name','[greatest]'),
  @least    += replace([iif],'col_name','[least]')
from #end
order by ref_ordinal desc;

select 
  @greatest as [greatest],
  @least    as [least]
;

z-create-table-foo.sql

-- https://chat.stackexchange.com/transcript/message/66238168#66238168
-- https://dba.stackexchange.com/a/199466/68127

create table dbo.foo (
  id int identity not null primary key,
  a int, 
  b int,
  c int,
  d int,
  e int,
  f int,
  g int,
  h int,
  z char(1)
);