champ_010
asked on
Sorting ASC DESC using CASE
I would like to sort a table depending on a value I pass to the stored procedure. This is what I have:
CREATE PROCEDURE [dbo].[GetVFMessages] (@topicID int,@sortBy bit) AS
SELECT tblVFMessages. *, vfTopicName FROM tblVFMessages
INNER JOIN tblVFTopics ON tblVFMessages.vfTopicID = tblVFTopics.vfTopicID
WHERE tblVFMessages.vfTopicID=@t opicID
ORDER BY
CASE
WHEN @sortBy =' ' THEN vfPostDate DESC
END,
CASE
WHEN @sortBY='0' THEN vfPostDate DESC
END,
CASE
WHEN @sortBy='1' THEN vfPostDate ASC
END;
GO
The DESC and ASC that I put in the different CASEs run an error. It works if I remove them. How can I sort the column vfPostDate by the value I pass in?
CREATE PROCEDURE [dbo].[GetVFMessages] (@topicID int,@sortBy bit) AS
SELECT tblVFMessages. *, vfTopicName FROM tblVFMessages
INNER JOIN tblVFTopics ON tblVFMessages.vfTopicID = tblVFTopics.vfTopicID
WHERE tblVFMessages.vfTopicID=@t
ORDER BY
CASE
WHEN @sortBy =' ' THEN vfPostDate DESC
END,
CASE
WHEN @sortBY='0' THEN vfPostDate DESC
END,
CASE
WHEN @sortBy='1' THEN vfPostDate ASC
END;
GO
The DESC and ASC that I put in the different CASEs run an error. It works if I remove them. How can I sort the column vfPostDate by the value I pass in?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ah, I seee....thanks!
CREATE PROCEDURE [dbo].[GetVFMessages] (@topicID int,@sortBy bit) AS
Set Concat_Null_Yields_Null Off
Declare @sql VarChar(8000)
Set @SQL =
'SELECT tblVFMessages. *, vfTopicName FROM tblVFMessages
INNER JOIN tblVFTopics ON tblVFMessages.vfTopicID = tblVFTopics.vfTopicID
WHERE tblVFMessages.vfTopicID = ' + Cast(@topicID As VarChar) + Char(10) +
'ORDER BY vfPostDate ' +
CASE
WHEN @sortBy = 1 Then 'ASC'
Else 'DESC'
END;
--Print @SQL
Exec(@SQL)
GO
But it looks like Angell's may work as well . . . .