Вообще-то это больной вопрос, и мучает он многих. Конкретные особенности реализации зависят от конкретного клиентского инструментария. Лично я для этих целей использую генерацию ошибки Raiserror со специально заданным кодом. Ошибка перехватывается на клиенте. Текст сообщения, собственно, представляет оповещение, которое обрабатывается соответствующим образом средствами клиента. Есть существенное неудобство использования данного метода. Генерация ошибки с помощью Raiserror может вызвать прерывание транзакции, а это не всегда допустимо.