FAQ : What is Oracle Predefined Exceptions equivalent in SQL Server

What is Oracle Predefined Exceptions
An exception is a runtime error or warning condition, which can be predefined or user-defined. Predefined exceptions are raised implicitly (automatically) by the runtime system. User-defined exceptions must be raised explicitly by RAISE statements. To handle raised exceptions, you write separate routines called exception handlers. PL/SQL declares predefined exceptions globally in package STANDARD, which defines the PL/SQL environment. So, you need not declare them yourself.
Here are few examples
NO_DATA_FOUND : A SELECT INTO statement returns no rows, or your program references a deleted element in a nested table or an uninitialized element in an index-by table. SQL aggregate functions such as AVG and SUM always return a value or a null. So, a SELECT INTO statement that calls an aggregate function never raises NO_DATA_FOUND. The FETCH statement is expected to return no rows eventually, so when that happens, no exception is raised.

TOO_MANY_ROWS : A SELECT INTO statement returns more than one row.

DUP_VAL_ON_INDEX : Your program attempts to store duplicate values in a database column that is constrained by a unique index.

Read more about Predefined exceptions here

What is SQL Server Equivalent?

Till SQL Server 2008 we do not have any equivalent. Ie there is no Predefined Exceptions in SQL Server. But this can be achieved by BEGIN TRY .. END TRY BEGIN CATCH … END CATCH. ERROR Number/Message combination.