I have a table that I need sorted on fields By @SortBy and @SortDirection,
for ID,PriorityID,stateType (column type is int) result is OK, but for Title (nvarchar) Query Result is:
Conversion failed when converting the nvarchar value 'Title Column value' to data type int.
Query:
CASE WHEN @SortDirection = 'ASC' THEN CASE @SortBy WHEN 'ID' THEN ID --int WHEN 'Title' THEN Title --nvarchar WHEN 'PriorityID' THEN [Priority] --int WHEN 'stateType' THEN [state] --int end END ASC ,case WHEN @SortDirection = 'DESC' THEN CASE @SortBy WHEN 'ID' THEN ID WHEN 'Title' THEN Title WHEN 'Priority' THEN [Priority] WHEN 'state' THEN [state] END END DESC
Advertisement
Answer
The types are different, and that is a problem for the case
expressions. The simplest method is a different case
for each possibility:
ORDER BY (CASE WHEN @SortDirection = 'ASC' AND @SortBy = 'ID' THEN ID END) ASC, (CASE WHEN @SortDirection = 'ASC' AND @SortBy = 'Title' THEN Title END) ASC, . . .