SQL query to get the SP name inside stored procedure.

Recently I had to scratch a SSIS package which was developed by Microsoft consultant. Got some interesting mechanisms while inspecting his stored procedures. Among those, the most noticed one was the technique to get the name of the stored procedure inside the same SP.If we see from the procedure execution point of view this is the current executing SP.

create PROCEDURE usp_WhatIsMyName
AS
BEGIN
    DECLARE @ProcName sysname = OBJECT_NAME(@@PROCID);
    select @ProcName --Returns usp_WhatIsMyName    
END
GO

This seems more like reflection in .Net where we can program the meta data. You may explore more on SQL meta data programming here.