SQL Server Impersonation Guide

Excellent question. Yes, if LoginA (or UserA) is granted IMPERSONATE on LoginB (or UserB), then LoginA (or UserA) can execute the EXECUTE AS statement whenever they want, without restriction:

EXECUTE AS LOGIN = 'LoginB';

or

EXECUTE AS USER = 'UserB';

then what is the purpose of a stored procedure with EXECUTE AS?

The purpose of the EXECUTE AS clause (part of the CREATE {module} statement) is to temporarily grant that IMPERSONATE permission only in the context of that module. This prevents you from needing to explicitly grant IMPERSONATE. The difference is, outside of the context of that module with EXECUTE AS {SomeUser}, whoever is executing that module shouldn't have the ability to impersonate that User. You don't want Logins / Users being able to impersonate others whenever they want. That is a security risk. The EXECUTE AS clause gives you the ability to let a principal impersonate another only when you want, not when they try to on their own (where you can't restrict what they do with those elevated permissions). So, you actually do want the EXECUTE AS clause, and do not want to grant IMPERSONATE to anyone ever (if it can be avoided, and most likely it can be).

For example: There is no TRUNCATE TABLE permission. If you want someone to be able to truncate a particular table (that they do not own, since they do have permission to do that) you could grant them IMPERSONATE on dbo. However, there is then no way to restrict their actions to only issuing TRUNCATE TABLE, and only on that particular table. That User can execute EXECUTE AS USER = 'dbo'; whenever they want, and then do whatever they want. However, by adding EXECUTE AS 'dbo' to the CREATE PROCEDURE .... AS TRUNCATE TABLE {TableName}; statement, whoever executes that module will still impersonate dbo, but only for the actions in that module, which here is just truncating a particular table.

Of course, the EXECUTE AS clause of a CREATE statement can only impersonate another User (database-level only). It cannot impersonate another Login (instance-level). This limitation is what leads too many people to enable TRUSTWORTHY so that a Login matching the same SID of the User being impersonated will be able to temporarily grant additional permissions. But this is quite dangerous, which is why the ideal (and far better) mechanism for temporarily elevating permissions is Module Signing.

For more details on why impersonation, as well as TRUSTWORTHY ON and Cross-DB Ownership Chaining, are bad, and how / why Module Signing is better, please see:

PLEASE, Please, please Stop Using Impersonation, TRUSTWORTHY, and Cross-DB Ownership Chaining