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

public class PubsProcs
{

	// uses SqlPipe and least expensive method of returning rows
	[SqlProcedure]
	public static void getAuthorsByState(SqlString state) 
	{
	}

	// Gets information about a single author
	// and returns data in an OUTPUT parameter
	[SqlProcedure]
	public static void getAuthorInfo(SqlString au_id, out SqlString au_info)
	{
		// 1. build a connection and command

		// 2. build command text
		// note that this will return a single row because
		// au_id is the primary key

		// 3. make a paramter to hold the author id

		// 4. put in the value of the author id

		// 5. use ExecuteReader, expect 
		// a single row will come back
		// 6. au_info is marked 'out' so you must create
		// a SqlString for it, as it is null when 
		// getAuthorInfo is called
		au_info = new SqlString();
		// 7. use String.Format to build the information string
		// 8. nothing to return beacuse value is sent back via au_info
		return;
	}
	// Get author info
	// use a SqlPipe to send info
	// back as message
	[SqlProcedure]
	public static void getAuthorMessage(SqlString au_id)
	{
		// 1. build a command

		// 2. build command text
		// note that this will return a single row because
		// au_id is the primary key

		// 3. make a paramter to hold the author id

		// 4. put in the value of the author id

		// 5. use use ExecuteReader, expect 
		// a single row will come back

		// 6. get the SqlPipe from the SqlContext

		// 7. use the SqlPipe.Send to send back the
		// author info as a message
	}
	// Previous function ignored an errors when
	// query was executed
	// Gets author info, but returns a customized error
	// message if ssn passed in does not correspond
	// to an author in pubs
	[SqlProcedure]
	public static SqlInt32 getAuthorWithErrors(SqlString au_id, out SqlString au_info)
	{
		SqlDataRecord rec = null;
		// 1. build a command
		// 2. build command text
		// 3. note that this will return a single row because
		// au_id is the primary key
		// 4. make a paramter to hold the author id
		// 5. put in the value of the author id
		// 6. use ExecuteReader, expect 
		// a single row will come back
		// 7. make SqlString to hold result
		// note that if you do not give this
		// string a value it will be null
		// 8. check to see if lookup was successful
		au_info = new SqlString();
		if (rec == null)
		{
			//au_info = String.Format("Author {0} is not found", au_id);
			//cmd.CommandText = ...
			// lookup was not successful, raise an error
			// use the error that you defined in the database
			return 0;
		}
		else
		{
			// lookup was successful, set au_info to information string
		}

		// nothing to return, either success returned author info in au_info
		// or error was raised
		return 0;
	}
}
