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

Hi Eduard,

When I have written last 4 lines instead on 5 lined in ClassMethod than my issue was resolved. But I have to write those 4 lines in all ClassMethod. Do you know any other place, where I  put these 4 common lines and it will work for all ClassMethods.

Thanks for you more support in advance.

Amit Prajapati