Error handling in SQL Server T-SQL scripts is not always the most graceful thing to deal with. With the release of Microsoft SQL Server 2005 you can now use TRY / CATCH statements. Here's a simple example of try / catch on sqlserver:

BEGIN TRY
	-- try / catch requires SQLServer 2005 
	-- run your code here
END TRY
BEGIN CATCH
	PRINT 'Error Number: ' + str(error_number()) 
	PRINT 'Line Number: ' + str(error_line())
	PRINT error_message()
	-- handle error condition
END CATCH

If you want to throw an exception with SQL Server you can use the RAISERROR() function, for example:

RAISERROR('Error Message', 18, 0);