using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;


public partial class StoredProcedures
{
  [Microsoft.SqlServer.Server.SqlProcedure]
  public static SqlInt32 getAuthorWithErrors(
    SqlString au_id, out SqlString au_info)
  {
    // -- In SQL Server Management Studio add custom message to be used
    // -- when an author cannot be found
    // sp_addmessage 50005, 16,
    //  'author with ssn: %s, not found in the database'
    // go

    // build a command
    SqlConnection conn = new SqlConnection("context connection=true");
    conn.Open();
    SqlCommand cmd = conn.CreateCommand();
    // build command text
    cmd.CommandText =
      "select address, city, state, zip" +
      " from authors where au_id = @au_id";

    // make a parameter to hold the author id
    cmd.Parameters.Add("@au_id", SqlDbType.VarChar);
    // put in the value of the author id
    cmd.Parameters[0].Value = au_id;

    SqlDataReader rec = cmd.ExecuteReader();
    // make SqlString to hold result
    // note that if you do not give this
    // string a value it will be null
    au_info = new SqlString();

    // check to see if lookup was successful
    if (rec.Read() == false)
    {
      rec.Close();
      // lookup was not successful, raise an error
      cmd.CommandText = "Raiserror (50005, 16, 1, '" +
        au_id.ToString() + "') with seterror";
      // use the try-catch with empty catch clause
      try { SqlContext.Pipe.ExecuteAndSend(cmd); }
      catch { }
      // this return statement will never be executed
      return 0;
    }
    else
    {
      // lookup was successful, set au_info to information string
      au_info = String.Format("{0} {1} {2} {3}",
        rec["address"], rec["city"], rec["state"], rec["zip"]);
      rec.Close();
    }
    // nothing to return, either success returned author info in au_info
    // or error was raised
    return 0;
  }

};
