Move from: bobsql.com
If I would have simply read the documentation (https://docs.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql?view=sql-server-ver15) the answer to my problem is answered.
Create the following procedure where foo does not exist. Error 208 – Invalid object name occurs when the select is executed.
create procedure sp_ExecTest
as
begin
BEGIN TRAN
exec(‘select * from foo’)
if (@@error <> 0)
rollback
end
Execute the procedure and error 208 is returned as expected.
exec sp_ExecTest
Msg 208, Level 16, State 1, Line 1
Invalid object name ‘foo’.
Use TSQL TRY/CATCH around the procedure execution.
BEGIN TRY
exec sp_ExecTest
END TRY
BEGIN CATCH