create FUNCTION Fn_MaxSal_emp
(
@dept int
)
returns varchar(100)
as
begin 
    declare @maxsal int
    set @maxsal=(select max(sal) from emp where deptno=@dept)
    if @maxsal is not null
    begin
    return @maxsal
    end
return(try_cast(@dept as varchar(100)) ' record not found') 
end

select dbo.fn_maxsal_emp(50) as maxsal

/* deptno =50 record is not there in table , so should get '50 record is not found' as output but gets the error message as given in title */

CodePudding user response：

It looks like you are are using another function than you have posted here. The error messages says that the varchar value " record 50 is not found" is not convertable to int. Which is correct. But the function you posted would never generate that varchar value. This function would produce "50 record not found"

Please check if you are calling the correct function, perhaps there is an old version in the database and not the version you posted here.

I tried your function here and it works: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=be0ea9d1e6715d77bbc8fa4773702f01 I just changed the returns a little bit because i think it's bad practice to do not specify an explicit cast.

CodePudding user response：

This error is created when first trying to call the function, before even reaching the try_cast() line, because you can't put the ** value from the error message in the @dept variable used to call the method in the first place. That's where the cast from varchar to int fails.