CREATE   PROCEDURE poAdvSearch (
	@number VARCHAR(255) = NULL,
	@statusid INT = NULL,
	@pr VARCHAR(255) = NULL,
	@ar VARCHAR(255) = NULL,
	@vendorid INT = NULL,
	@event VARCHAR(255) = NULL,
	@startdate DATETIME = NULL,
	@enddate DATETIME = NULL,
	@buyer VARCHAR(255) = NULL,
	@typeid INT = NULL,
	@category VARCHAR(255) = NULL,
	@item VARCHAR(255) = NULL
) AS
	SET NOCOUNT ON
	
	DECLARE @categories TABLE (CategoryID INT)
	DECLARE @items TABLE(ItemID INT)

	INSERT INTO @categories
		SELECT	CatalogCategoryID
		  FROM	CatalogCategories
		 WHERE	Name LIKE '%' + @category + '%'
			OR	@category IS NULL
	
	INSERT INTO @items
		SELECT	ItemID
		  FROM	vActiveCatalogItems
		 WHERE	Name LIKE '%' + @item + '%'
			OR	@item IS NULL

	  SELECT	po.ID PurchaseOrderID,
				v.Name Vendor,
				po.POPrefix + CONVERT(VARCHAR, po.PONumber) + po.POPostfix Number,
				CONVERT(VARCHAR, ISNULL(po.PRID, '')) PR, 
				ISNULL(ar.ControlNumber, '') AR,
				pot.Name Type,
				pos.Name Status,
				po.OrderDate,
				ISNULL((SELECT SUM(Quantity * UnitPrice) FROM PurchaseOrderItems WHERE PurchaseOrderID = po.ID GROUP BY PurchaseOrderID), 0) Total
	    FROM	PurchaseOrders po
				INNER JOIN PurchaseOrderTypes pot
					ON	po.TypeID = pot.ID
				INNER JOIN PurchaseOrderStatuses pos
					ON	po.StatusID = pos.ID
				INNER JOIN Vendors v
					ON	po.VendorID = v.VendorID
				LEFT JOIN AppropriationsRequests ar
					ON	po.ARID = ar.AppropriationsRequestID
				LEFT JOIN PurchaseOrderItems poi
					ON	po.ID = poi.PurchaseOrderID
				LEFT JOIN CatalogItems ci
					ON	poi.CatalogItemID = ci.CatalogItemID
				LEFT JOIN PurchaseOrderEvents poe
					ON	po.ID = poe.PurchaseOrderID
	   WHERE	((po.POPrefix + CONVERT(VARCHAR, po.PONumber) + po.POPostfix) LIKE '%' + @number + '%'
		  OR	@number IS NULL)
		 AND	(po.StatusID = @statusid
		  OR	@statusid IS NULL)
		 AND	(CONVERT(VARCHAR, po.PRID) LIKE '%' + @pr + '%'
		  OR	@pr IS NULL)
		 AND	(ar.ControlNumber LIKE '%' + @ar + '%'
		  OR	@ar IS NULL)
		 AND	(po.VendorID = @vendorid
		  OR	@vendorid IS NULL)
		 AND	(poe.Event = @event
		  OR	@event IS NULL)
		 AND	((CONVERT(DATETIME, poe.Value) BETWEEN @startdate AND @enddate)
		  OR	(@startdate IS NULL AND CONVERT(DATETIME, poe.Value) <= @enddate)
		  OR	(@enddate IS NULL AND CONVERT(DATETIME, poe.Value) >= @startdate)
		  OR	(@startdate IS NULL AND @enddate IS NULL))
		 AND	(po.Buyer LIKE '%' + @buyer + '%'
		  OR	@buyer IS NULL)
		 AND	(po.TypeID = @typeid
		  OR	@typeid IS NULL)
		 AND	(ci.CategoryID IN (SELECT CategoryID FROM @categories) OR @category IS NULL)
		 AND	(ci.CatalogItemID IN (SELECT ItemID FROM @items) OR @item IS NULL)
	GROUP BY	po.ID,
				v.Name,
				po.POPrefix + CONVERT(VARCHAR, po.PONumber) + po.POPostfix,
				CONVERT(VARCHAR, ISNULL(po.PRID, '')), 
				ISNULL(ar.ControlNumber, ''),
				pot.Name,
				pos.Name,
				po.OrderDate

Alter Procedure up_parmsel_list(
    @stockbooknum int = null, 
    @regnum varchar(15) = null, 
    @make varchar(20) = null, 
    @model varchar(20) = null, 
    @enginesize int = null, 
    @trim varchar(20) = null, 
    @fuelType int = null, 
    @shellType int = null, 
    @colour varchar(20) = null, 
    @chassisNumber varchar(20) = null
)
as

select
    vehicle.vehicle_id,
    stock_book_number,
    make,
    model,
    colour,
    registration,
    engine_size,
    trim,
    shell_types.shell_type_id,
    fuel_types.fuel_type_id
from
    vehicles
    left outer join shell_types -- I think you probably want inner joins here
        on vehicles.shell_type = shell_types.shell_type_id
    left outer join fuel_types -- I think you probably want inner joins here
        on vehicles.fuel_type = fuel_types.fuel_type_id 
where
    vehicles.stock_book_number = isnull(@stockbooknum, vehicles.stock_book_number) and
    charindex(isnull(@regnum, vehicle.registration), vehicles.registration) > 0 and
    charindex(isnull(@make, vehicle.make), vehicles.make) > 0
    -- etc etc etc
order by
    vehicles.stock_book_number
    
go

quote:

---

Scrape the dynamic SQL . . . It is hard to read, it requires granting permissions on your base tables, its ugly and it smells funny.

---