CREATE PROCEDURE SP_OwnersListing
(
@OwnersId int = NULL
,@SearchString nvarchar(max) = NULL
,@SortString nvarchar(max) = ‘ ‘
,@PageNumber int = 1
)
AS
BEGIN
DECLARE
@OLSortString nvarchar(max),
@PageSize int
SET @OLSortString = @SortString
SET @PageSize = 10
SET @SearchString = LTRIM(RTRIM(@SearchString))
SELECT Owners.Id As OwnerID, RL.PropertyId, RL.Id As ListingId,
Concat(A.Number,’,’,A.Street,’,’,A.Suburb,’,’,A.City)As Location,
P.Bedroom, P.Bathroom, P.ParkingSpace, RL.Title,
RWL.UpdatedOn As NewApplication, RLM.NewFileName As Photo
FROM Owners
Left Join RentalWatchList as RWL
On RWL.PersonId = Owners.Id
Left Join RentalListing As RL
On RWL.RentalListingId = RL.Id
Left Join Property As P
On RL.PropertyId = P.Id
Left Join Address as A
On P.AddressId = A.AddressId
Left Join TargetRentType As TRT
On P.TargetRentTypeId = TRT.Id
Left Join RentalListingMedia As RLM
On RL.Id = RLM.RentalListingId
WHERE (Owners.Id = @OwnersId OR @OwnersId IS NULL)
AND (@SearchString IS NULL OR TITLE LIKE ‘%’+ @SearchString+’%’
OR A.Number LIKE ‘%’+@SearchString+’%’OR A.Street LIKE’%’+@SearchString+’%’
OR A.Suburb LIKE’%’+@SearchString+’%’ OR A.City LIKE’%’+@SearchString+’%’)
AND (@PageNumber = @PageNumber OR @PageNumber = 1)
AND(@PageSize = @PageSize OR @PageSize = 10)
AND(@OLSortString = @SortString OR @SortString = ‘ ‘)
ORDER BY
CASE WHEN @SortString = ‘TITLE’ Then RL.TITLE END ASC,
CASE WHEN @SortString = ‘NEW APPLICATION’ Then RWL.UpdatedOn END ASC,
CASE WHEN @SortString = ‘EARLIEST LISTING’ Then RL.CreatedOn END ASC,
CASE WHEN @SortString = ‘TITLE DESC’ Then RL.TITLE END DESC,
CASE WHEN @SortString = ‘NEW APPLICATION DESC’Then RWL.UpdatedOn END DESC,
CASE WHEN @SortString = ‘LATEST LISTING’ Then RL.CreatedOn END DESC
OFFSET (@PageNumber – 1)*@PageSize ROWS
FETCH NEXT @PageSize ROWS Only
END
Calling the Procedure
For Instance
EXEC SP_OwnersListing @SortString = ‘TITLE’
OR EXEC SP_OwnersListing @….
any given parameters
