The system variable @@ERROR is a quick and easy way to retrieve the error number from the last statement run and use it in your code.

RAISERROR(18452, 16, 1, 1, N'');
PRINT @@Error;

Note: I’m using RAISERROR to cheat just a little bit rather than actually creating a real error situation.

The trick with @@ERROR is that it goes away on the very next command.

RAISERROR(18452, 16, 1, 1, N'');
IF @@ERROR > 0
	PRINT @@ERROR; 

This is always going to print a 0, because even though @@ERROR was greater than 0 in the condition, once the condition is over, a non-failing command has occurred and @@ERROR is set back to 0. This means you should always save the value into a variable in the very next command after the one you think might produce an error.

DECLARE @error INT;
RAISERROR(18452, 16, 1, 1, N'');
SET @error = @@ERROR;
IF @error > 0
	PRINT @error;

Now we have a variable that contains that last error command that we can both test and use.

Of course all of that said, I’m doing this post because I see @@ERROR frequently in legacy code and it sometimes comes in handy when doing temporary debugging code but for newer permanent code you are almost always going to be better off using TRY .. CATCH. It gives you quite a few extra options, for example ERROR_NUMBER() and ERROR_MESSAGE() which return information about the error going into the CATCH block.