Sample Solution for Creating Stored Procedure using Search, Sort and Pagination with Parameters

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

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s