GO

CREATE OR ALTER FUNCTION udf_ClientWithCigars(@name NVARCHAR(30))
RETURNS INT
AS
BEGIN
RETURN (SELECT COUNT(cc.CigarId)
FROM Clients AS c
JOIN ClientsCigars AS cc ON c.Id = cc.ClientId
WHERE c.FirstName = @name
)
END

GO
SELECT dbo.udf_ClientWithCigars('Betty')