Why does my function return NULL while the same query gives me a result?

Question

When I run this query, I get an expected result, i.e. a single UNIQUEIDENTIFIER:

select top 1 ec.NewValue from Events e
join EventChanges ec on e.Id = ec.EventId
where e.EntityChangedId = 'aceaea4b-e4d1-45ea-84f7-8ecda1a0b20e'
    and ec.FieldChanged = 'Company.CallFrequencyId'
    and e.EventDate <= '2022-07-11 15:32:00.0000000 +00:00'
order by e.EventDate desc

However, when I create a function containing the exact same query and call it, I get NULL:

CREATE FUNCTION [dbo].[GetCallFrequencyIdAtDate](@companyId VARCHAR, @date DATETIME2)
RETURNS VARCHAR
AS BEGIN
RETURN (
    select top 1 ec.NewValue from Events e
    join EventChanges ec on e.Id = ec.EventId
    where e.EntityChangedId = @companyId
        and ec.FieldChanged = 'Company.CallFrequencyId'
        and e.EventDate <= @date
    order by e.EventDate desc
)
END
GO

SELECT dbo.GetCallFrequencyIdAtDate('aceaea4b-e4d1-45ea-84f7-8ecda1a0b20e', '2022-07-11 15:32:00.0000000 +00:00')

What am I doing wrong?

I tried changing the return type to UNIQUEIDENTIFIER, VARCHAR(MAX), NVARCHAR(MAX)

Answer 1

Declaring @companyId as VARCHAR is truncating the input to a single character. Just using VARCHAR is essentially the same as using VARCHAR(1). Always specify the length of VARCHAR variables and columns.

But since you're using UNIQUEIDENTIFIER just use that for your variable

DECLARE @companyId UNIQUEIDENTIFIER.

Make sure to also update your return type.