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 void search_orders_cs (
       SqlInt32    Orderid,
       SqlDateTime Fromdate,
       SqlDateTime Todate,
       SqlMoney    Minprice,
       SqlMoney    Maxprice,
       SqlString   Custid,
       SqlString   Custname,
       SqlString   City,
       SqlString   Region,
       SqlString   Country,
       SqlInt32    Prodid,
       SqlString   Prodname,
       SqlBoolean  Debug)

{
    string Query;
    SqlCommand Command = new SqlCommand();

    Query = @"SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,
                     c.CustomerID, c.CompanyName, c.Address, c.City,
                     c.Region, c.PostalCode, c.Country, c.Phone,
                     p.ProductID, p.ProductName, p.UnitsInStock,
                     p.UnitsOnOrder
              FROM   dbo.Orders o
              JOIN   dbo.[Order Details] od ON o.OrderID = od.OrderID
              JOIN   dbo.Customers c ON o.CustomerID = c.CustomerID
              JOIN   dbo.Products p ON p.ProductID = od.ProductID
              WHERE  1 = 1 ";

    if (! Orderid.IsNull) {
       Query += " AND o.OrderID = @orderid " +
                " AND od.OrderID = @orderid";
       Command.Parameters.Add("@orderid", SqlDbType.Int);
       Command.Parameters["@orderid"].Value = Orderid;
       Command.Parameters["@orderid"].Direction = ParameterDirection.Input;
    }

    if (! Fromdate.IsNull) {
       Query += " AND o.OrderDate >= @fromdate";
       Command.Parameters.Add("@fromdate", SqlDbType.DateTime);
       Command.Parameters["@fromdate"].Value = Fromdate;
       Command.Parameters["@fromdate"].Direction = ParameterDirection.Input;
    }

    if (! Todate.IsNull) {
       Query += " AND o.OrderDate <= @todate";
       Command.Parameters.Add("@todate", SqlDbType.DateTime);
       Command.Parameters["@todate"].Value = Todate;
       Command.Parameters["@todate"].Direction = ParameterDirection.Input;
    }

    if (! Minprice.IsNull) {
       Query += " AND od.UnitPrice >= @minprice";
       Command.Parameters.Add("@minprice", SqlDbType.Money);
       Command.Parameters["@minprice"].Value = Minprice;
       Command.Parameters["@minprice"].Direction = ParameterDirection.Input;
    }

    if (! Maxprice.IsNull) {
       Query += " AND od.UnitPrice <= @maxprice";
       Command.Parameters.Add("@maxprice", SqlDbType.Money);
       Command.Parameters["@maxprice"].Value = Maxprice;
       Command.Parameters["@maxprice"].Direction = ParameterDirection.Input;
    }

    if (! Custid.IsNull) {
       Query += " AND o.CustomerID = @custid" +
                " AND c.CustomerID = @custid";
       Command.Parameters.Add("@custid", SqlDbType.NChar, 5);
       Command.Parameters["@custid"].Value = Custid;
       Command.Parameters["@custid"].Direction = ParameterDirection.Input;
    }

    if (! Custname.IsNull) {
       Query += " AND c.CompanyName LIKE @custname + '%'";
       Command.Parameters.Add("@custname", SqlDbType.NVarChar, 40);
       Command.Parameters["@custname"].Value = Custname;
       Command.Parameters["@custname"].Direction = ParameterDirection.Input;
    }

    if (! City.IsNull) {
       Query += " AND c.City = @city";
       Command.Parameters.Add("@city", SqlDbType.NVarChar, 15);
       Command.Parameters["@city"].Value = City;
       Command.Parameters["@city"].Direction = ParameterDirection.Input;
    }

    if (! Region.IsNull) {
       Query += " AND c.Region = @region";
       Command.Parameters.Add("@region", SqlDbType.NVarChar, 15);
       Command.Parameters["@region"].Value = Region;
       Command.Parameters["@region"].Direction = ParameterDirection.Input;
    }

    if (! Country.IsNull) {
       Query += " AND c.Country = @country";
       Command.Parameters.Add("@country", SqlDbType.NVarChar, 15);
       Command.Parameters["@country"].Value = Country;
       Command.Parameters["@country"].Direction = ParameterDirection.Input;
    }

    if (! Prodid.IsNull) {
       Query += " AND od.ProductID = @prodid" +
                " AND p.ProductID = @prodid";
       Command.Parameters.Add("@prodid", SqlDbType.Int);
       Command.Parameters["@prodid"].Value = Prodid;
       Command.Parameters["@prodid"].Direction = ParameterDirection.Input;
    }

    if (! Prodname.IsNull) {
       Query += " AND p.ProductName LIKE @prodname + '%'";
       Command.Parameters.Add("@prodname", SqlDbType.NVarChar, 40);
       Command.Parameters["@prodname"].Value = Prodname;
       Command.Parameters["@prodname"].Direction = ParameterDirection.Input;
    }

    Query += " ORDER BY o.OrderID";

using (SqlConnection Connection = new SqlConnection("context connection=true"))
{
  Connection.Open();

  if (Debug) {
     SqlContext.Pipe.Send(Query);
  }

  Command.CommandType = CommandType.Text;
  Command.CommandText = Query;
  Command.Connection  = Connection;
  SqlContext.Pipe.ExecuteAndSend(Command);
}
}
};