SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create or alter function dbo.foo()
returns table 
with schemabinding
as
return (
       select 
          x = OBJECT_SCHEMA_NAME(@@PROCID) 
          ,y =OBJECT_NAME(@@PROCID)
);
select * from dbo.foo();


x
y


null
null


create or alter proc dbo.bar
as
begin
   select 
        x= OBJECT_SCHEMA_NAME(@@PROCID)
        , y = OBJECT_NAME(@@PROCID)
end;
exec dbo.bar;


x
y


dbo
bar


create or alter function dbo.baz()
returns varchar(100)
as
begin
    return isnull(OBJECT_SCHEMA_NAME(@@PROCID),'NULL') + '.' + isnull(OBJECT_NAME(@@PROCID),'NULL');
end;
select dbo.baz()


(No column name)


NULL.NULL