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:
x
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,
. . .