USE Northgale go CREATE PROCEDURE search_orders_0 @orderid int = NULL, @status char(1) = NULL, @fromdate date = NULL, @todate date = NULL, @minprice decimal(10,2) = NULL, @maxprice decimal(10,2) = NULL, @custid nchar(5) = NULL, @custname nvarchar(40) = NULL, @city nvarchar(25) = NULL, @region nvarchar(15) = NULL, @country nvarchar(15) = NULL, @prodid int = NULL, @prodname nvarchar(40) = NULL AS SELECT o.OrderID, o.OrderDate, o.EmployeeID, o.Status, c.CustomerID, c.CustomerName, c.Address, c.City, c.Region, c.PostalCode, c.Country, c.Phone, od.UnitPrice, od.Quantity, p.ProductID, p.ProductName, p.UnitsInStock, p.UnitsOnOrder FROM Orders o JOIN [Order Details] od ON o.OrderID = od.OrderID JOIN Customers c ON o.CustomerID = c.CustomerID JOIN Products p ON p.ProductID = od.ProductID WHERE (o.OrderID = @orderid OR @orderid IS NULL) AND (o.Status = @status OR @status IS NULL) AND (o.OrderDate >= @fromdate OR @fromdate IS NULL) AND (o.OrderDate <= @todate OR @todate IS NULL) AND (od.UnitPrice >= @minprice OR @minprice IS NULL) AND (od.UnitPrice <= @maxprice OR @maxprice IS NULL) AND (o.CustomerID = @custid OR @custid IS NULL) AND (c.CustomerName LIKE @custname + '%' OR @custname IS NULL) AND (c.City = @city OR @city IS NULL) AND (c.Region = @region OR @region IS NULL) AND (c.Country = @country OR @country IS NULL) AND (od.ProductID = @prodid OR @prodid IS NULL) AND (p.ProductName LIKE @prodname + '%' OR @prodname IS NULL) ORDER BY o.OrderID