SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create or alter function dbo.MyFunction
(
    @Param1 int,
    @Param2 int
)
returns float as
begin

return (1 * 1.0) / nullif(@Param2, 0)
end
drop table if exists #MyTable
create table #MyTable
(
    Col1 int,
    Col2 int,
    Col3 int
)

insert into #MyTable
values
    (5, 0, 10),
    (3, 1, 10),
    (1, -10, -5)

declare @Param1 int = 5, @Param2 int = 0

declare @ScalarResult float = dbo.MyFunction(@Param1, @Param2)

SELECT 
    Col1,
    Col2,
    Col3 = isnull(dbo.MyFunction(Col1, Col2), Col3),
    Col4 = isnull(@ScalarResult, Col3)
from #MyTable


SELECT 
    Col1,
    Col2,
    Col3 = isnull(@ScalarResult, Col3)
from #MyTable

-- Different ternary condition, checking for negative numbers
select @ScalarResult = dbo.MyFunction(1, -10)

select 
    Col1,
    Col2,
    Col3 = iif(@ScalarResult < 0, Col3, @ScalarResult)
from #MyTable

-- using columns as inputs, or just making use of a cross apply
select 
    Col1,
    Col2,
    Col3 = iif(ScalarResult < 0, Col3, ScalarResult),
    Col4 = iif(ColumnResult < 0, Col3, ColumnResult)
from #MyTable
cross apply
(
    select 
        ColumnResult = dbo.MyFunction(Col1, Col2),
        ScalarResults =dbo.MyFunction(@Param1, @Param2)

) f


Col1
Col2
Col3
Col4


5
0
10
10

3
1
1
10

1
-10
-0.1
-5




Col1
Col2
Col3


5
0
10

3
1
10

1
-10
-5




Col1
Col2
Col3


5
0
10

3
1
10

1
-10
-5


Msg 207 Level 16 State 1 Line 43
Invalid column name 'ScalarResult'.

drop function if exists dbo.MyFunction
go
Msg 102 Level 15 State 1 Line 2
Incorrect syntax near 'go'.