j.mani j.mani - 21 days ago 7
SQL Question

Scalar-Valued Function with Parameters and output is null but iam expecting sum(marks)

Function:

create function fn_with_parameter
(
@id int=null
)
returns int
with returns null on null input
as
begin
declare @total int
select @total=sum(marks) from test3
where id=@id
return @total
end


Select:

select dbo.fn_with_parameter(default);

Answer

Modify your function as :

create function fn_with_parameter
(
@id int=null
)
returns int
--with returns null on null input 
as
begin 
declare @total int=0
if isnull(@id,'') = ''
set @id = ''

select @total=sum(marks) from test3
where isnull(id,'')=@id
return isnull(@total,0)
end

select dbo.fn_with_parameter(default);