Sunday, July 18, 2004
SQL Server Forums at SQLTeam.com - Article: Dynamic ORDER BY
SQL Server Forums at SQLTeam.com - Article: Dynamic ORDER BY
Syntax error converting datetime from character string.
ORDER BY CASE WHEN ... THEN ...
To get around the datatype problem, use seperate group CASE statements for each datatype.
ORDER BY
CASE WHEN @SORTBY = 1 THEN Numeric1
WHEN @SORTBY = 2 THEN Numeric2
END,
CASE WHEN @SORTBY = 3 THEN VarChar1
WHEN @SORTBY = 4 THEN VarChar2
END DESC,
CASE WHEN @SORTBY = 5 THEN DateTime1
END
Note that each case block is seperated by a comma (after END) except the last block. Also note that you can use ASC or DESC for each CASE statement group.
Syntax error converting datetime from character string.
ORDER BY CASE WHEN ... THEN ...
To get around the datatype problem, use seperate group CASE statements for each datatype.
ORDER BY
CASE WHEN @SORTBY = 1 THEN Numeric1
WHEN @SORTBY = 2 THEN Numeric2
END,
CASE WHEN @SORTBY = 3 THEN VarChar1
WHEN @SORTBY = 4 THEN VarChar2
END DESC,
CASE WHEN @SORTBY = 5 THEN DateTime1
END
Note that each case block is seperated by a comma (after END) except the last block. Also note that you can use ASC or DESC for each CASE statement group.
