CREATE procedure rolyrolls.proc_Search
	@TypeID int, 
	@MakeID int, 
	@YearFrom smallint, 
	@YearTo smallint, 
	@MilesFrom int, 
	@MilesTo int, 
	@ZIP nchar(5), 
	@Mile nchar(5)
as
	DECLARE @ZIPCode nchar(5)
	SET @ZIPCode = @ZIP
	
	DECLARE @Miles REAL
	SET @Miles = @Mile
	
	DECLARE @RowCount int
	SELECT @RowCount = Count(*) FROM ZipCodes.dbo.ZIPCodes WHERE ZIPCode = @ZIPCode AND CityType = 'D'
	
	if @RowCount > 0
begin
	SELECT Color.Color AS [Int], Color_1.Color AS Ext, DealershipModel.*, Model.*, Make.*, Type.*, Condition.*, 
Dealership.*, MakeType.*, ClassInvoiceDetail.*, ClassInvoice.*, ClassTypePrice.*, ClassType.*, ZipCodes.dbo.DistanceAssistant
(z.Latitude,z.Longitude,r.Latitude,r.Longitude) As Distance
	FROM Type INNER JOIN ((MakeType INNER JOIN (Make INNER JOIN Model ON Make.MakeID = Model.MakeID) ON MakeType.MakeTypeID 
= Make.MakeTypeID) INNER JOIN (Dealership INNER JOIN (Condition INNER JOIN (Color INNER JOIN ((ClassType INNER JOIN 
ClassTypePrice ON ClassType.ClassTypeID = ClassTypePrice.ClassTypeID) INNER JOIN ((ClassInvoice INNER JOIN ClassInvoiceDetail ON 
ClassInvoice.ClassInvoiceID = ClassInvoiceDetail.ClassInvoiceID) INNER JOIN (DealershipModel INNER JOIN Color AS Color_1 ON 
DealershipModel.ExtColorID = Color_1.ColorID) ON ClassInvoiceDetail.ClassInvoiceDetailID = DealershipModel.ClassInvoiceDetailID) 
ON ClassTypePrice.ClassTypePriceID = ClassInvoiceDetail.ClassTypePriceID) ON Color.ColorID = DealershipModel.IntColorID) ON 
Condition.ConditionID = DealershipModel.ConditionID) ON Dealership.DealershipID = ClassInvoice.DealershipID) ON Model.ModelID = 
DealershipModel.ModelID) ON Type.TypeID = DealershipModel.TypeID INNER JOIN zipcodes.dbo.ZipCodes z ON 
Dealership.DealershipZipCode = z.ZIPCode, ZipCodes.dbo.RadiusAssistant(@ZIPCode,@Miles) r
	WHERE ((((Model.MakeID)=@MakeID) OR ((Type.TypeID)=@TypeID) OR (((DealershipModel.DealershipModelYear) Between @YearFrom 
And @YearTo) AND ((DealershipModel.Mileage) Between @MilesFrom And @MilesTo) AND ((Model.MakeID)=@MakeID))) AND 
((DealershipModel.DealershipModelAvailable)=1) AND ((DealershipModel.DealershipModelDeleted)=0) AND 
((Dealership.DealershipActive)=1) AND ((Dealership.DealershipApproved)=1) AND ((MakeType.MakeType)='normal') AND 
((ClassInvoiceDetail.ClassInvoiceDetailExpired)=0) AND ((ClassInvoice.ClassInvoiceVoid)=0) AND 
((DealershipModel.DealershipModelRenew)=0) AND ((ClassInvoiceDetail.ClassInvoiceDetailPaymentDue)=0) AND z.Latitude <= r.MaxLat 
AND z.Latitude >= r.MinLat AND z.Longitude <= r.MaxLong AND z.Longitude >= r.MinLong AND CityType = 'D' AND ZIPCodeType <> 'M' 
AND ZipCodes.dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) <= @Miles)
	ORDER BY Distance , DealershipModel.DealershipModelYear DESC , DealershipModel.Mileage;
end
GO

SELECT *, Color.Color AS [Int], Color_1.Color AS Ext, ZipCodes.dbo.DistanceAssistant
(z.Latitude,z.Longitude,r.Latitude,r.Longitude) As Distance

SELECT Color.Color AS [Int], Color_1.Color AS Ext, ZipCodes.dbo.DistanceAssistant
(z.Latitude,z.Longitude,r.Latitude,r.Longitude) As Distance, *