As per Microsoft docs, dm_exec_sql_text returns the text of the SQL batch that is identified by the specified sql_handle while dm_exec_input_buffer returns information about statements submitted to an instance of SQL Server.
I used DBCC INPUTBUFFER for getting the last executed SQL. But it seems both dm_exec_sql_text and dm_exec_input_buffer are replacement DBCC INPUTBUFFER. If this is the case then what is the main difference between them? When should we use one of them?
sys.dm_exec_input_buffer represents only the batch or RPC call that began the request, in other words the command initiated by the client directly, not any later procedures or triggers called. As noted in the documentation, you can only pass a session_id and request_id. This is the equivalent of DBCC INPUTBUFFER as you have noted.
Meanwhile, sys.dm_exec_sql_text can be passed an sql_handle or a plan_handle, which can be got from various places, not least from sys.dm_exec_requests, which means that you can get the text of the currently executing statement, not just the initial batch.
