CREATE PROCEDURE MyTestStoredProcedure @Text varchar(255) 
SET NOCOUNT ON
RAISERROR( 'Серьёзная ошибка!', 16, 1 )
RETURN(0)
SELECT 1

var Connection = new SqlConnection("");
var Adapter = new SqlDataAdapter();
var Command = new SqlCommand();
var ds = new DataTable();
Command.Connection = Connection;
Adapter.SelectCommand = Command;
Command.CommandType = CommandType.StoredProcedure;
Command.CommandText = "MyTestStoredProcedure";
Command.Parameters.AddWithValue("Text", "Hello world");
Adapter.SelectCommand = Command;
Connection.Open();
Adapter.Fill(ds);

using (SqlDataReader reader = Command.ExecuteReader())
          {
                while (reader.NextResult()) ;
           }