Is there anyway we can get current stored procedure name using T-SQL

Try:OBJECT_NAME(@@PROCID)

I am not sure why you need it but you could either set a variable at the top of the SP. If it is to determine which procedure an error is thrown then 2005 has ERROR_PROCEDURE() which you can implement in the

TRY CATCH clause.

If you are using 2000 and it is to display in an error message then I would do something like

CREATE PROCEDURE dbo.MyProcedureName

AS

DECLARE @ProcName varchar(200), @Err int, @ErrMsg varchar(500)

SET @ProcName = 'MyProcedureName', @Err = 0, @ErrMsg = ''

/*CODE HERE*/

SELECT @Err = @@ERROR, @ErrMsg = 'Description of previous line of code'

IF @Err <> 0

BEGIN

    SELECT @ErrMsg = 'Error in ' + @ProcName + ': ' + @ErrMsg

    RAISERROR(@ErrMsg, 16, 1)

END

RETURN @Err

GO