SET NOCOUNT ON;
	Declare @StartIndex int;
		
		
Set @PropCount=(SELECT COUNT(property_id ) FROM [dbo].[vwResidentialProperty]
	WHERE (@Area is null or parentid = @Area)
			and (@locality is null or CharIndex( [dbo].[vwResidentialProperty].locid, @Locality)>0) 
			and askprice between isnull(@MinPrice,0) and isnull(@MaxPrice ,5000000)
			and bedrooms between isnull(@MinBed,0) and isnull(@MaxBed ,10)
			and (@PropType is null or charindex( [dbo].[vwResidentialProperty].propertytype,@proptype)>0)
			and floorarea between isnull(@MinFloor,0) and isnull(@MaxFloor,5000000));
			
		/*Check to see if the Number of Properties returned is less than actual page size if it is then set the start Index
		to the Property Count */
		if @PropCount < @Numrow
		begin
			set @StartIndex =  1;
		end;
		else
		begin
		set @StartIndex = (@PageIndex * @Numrow) + 1;
		end;
with Property As
(

	SELECT top 100 percent 
				case @SortOrder
				 when 0 then ROW_NUMBER() OVER (order by [dbo].[vwResidentialProperty].askprice  Asc) 
				 when 1 then ROW_NUMBER() OVER (order by [dbo].[vwResidentialProperty].askprice desc) 
				 when 2 then ROW_NUMBER() OVER (order by [dbo].[vwResidentialProperty].dateadded desc) 
				end as row,
	    [dbo].[vwResidentialProperty].property_id ,  [dbo].[vwResidentialProperty].Address,  [dbo].[vwResidentialProperty].askprice
	     , [dbo].[vwResidentialProperty].dateadded, [dbo].[vwResidentialProperty].Saleterm ,[dbo].[vwResidentialProperty].locid,
		[dbo].[property].adbrief
	FROM  [dbo].[vwResidentialProperty]
	inner join  [dbo].[property] on [dbo].[property].property_id =  [dbo].[vwResidentialProperty].property_id
	WHERE (@Area is null or parentid = @Area)
			and (@locality is null or CharIndex( [dbo].[vwResidentialProperty].locid, @Locality)>0) 
			and  [dbo].[vwResidentialProperty].askprice between isnull(@MinPrice,0) and isnull(@MaxPrice ,5000000)
			and  [dbo].[vwResidentialProperty].bedrooms between isnull(@MinBed,0) and isnull(@MaxBed ,10)
			and (@PropType is null or charindex( [dbo].[vwResidentialProperty].propertytype,@proptype)>0)
			and  [dbo].[vwResidentialProperty].floorarea between isnull(@MinFloor,0) and isnull(@MaxFloor,5000000)
			
	
)
			Select  Property_ID, Address as 'Address', askprice as 'Price', adbrief as 'Description', Saleterm, dateadded
			FROM Property 
			WHERE Row BETWEEN 
				@Startindex and (@startindex  + @Numrow )-1
				order by case @SortOrder
				when 0 then askprice 
				when 1 then   askprice -1
				--when 2 then  dateadded -1
				end;