I have already tried that but it is not working.

Thanks for your suggestion with example code.

Can you please advise how we get the data using this Search Method, because you did not write any return statement.

As per your suggestion, I have changed the query and it is working fine.

But I have to implement direction of sorting dynamically and it is not working. Please give your expert advise.

In below code, bold highlighted not working and give me compile time error.

select * from (

SELECT TOP ALL FirstName, MiddleName, LastName, Email, UserType

FROM LISDB.ExternalUsers

WHERE (ISNULL(:objSearch.FirstName, '') = '' OR FirstName LIKE :objSearch.FirstNameSearch)

AND (ISNULL(:objSearch.LastName, '') = '' OR LastName LIKE :objSearch.LastNameSearch)

AND (ISNULL(:objSearch.Email, '') = '' OR Email LIKE :objSearch.EmailSearch)

-- Order by :objSearch.SortingField

Order by

CASE :objSearch.SortingField

WHEN 'FirstName' THEN FirstName

WHEN 'MiddleName' THEN MiddleName

END

CASE :objSearch.IsDecending

WHEN 1 THEN DESC

WHEN 0 THEN ASC

END

)

WHERE %vid BETWEEN :objSearch.StartIndex AND :objSearch.EndIndex