Why do I get "Conversion failed when converting the varchar value ' 50 record is not found' to data type int." error while calling the below function

Question

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 in the table, so I should get '50 record is not found' as output but gets the error message as shown in the title

Answer 1

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.

Answer 2

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.