Sonic Sonic - 3 months ago 7
SQL Question

Dynamic ORDER BY in stored procedure not working as expected

This is my stored procedure to search a gallery of images uploaded by users:

ALTER PROCEDURE dbo.sp_SearchGallery
(
@strSearchTerm NVARCHAR(50) = NULL,
@strCategory NVARCHAR(50) = NULL,
@nUserId INT = NULL,
@nSortBy INT = 0,
@nSortDesc BIT = 0,
@nPage INT = 1,
@nPageSize INT = 10
) AS

SET NOCOUNT ON

DECLARE @nSortSwitch INT = 1
IF @nSortDesc = 1 BEGIN
SET @nSortSwitch = -1
END

DECLARE @FirstRec INT = (@nPage - 1) * @nPageSize
DECLARE @LastRec INT = (@nPage * @nPageSize + 1)

; WITH rowQueryResults AS
(
SELECT *, ROW_NUMBER() OVER
(
ORDER BY
CASE @nSortBy
WHEN 0 THEN Date
WHEN 1 THEN Title
WHEN 2 THEN Description
WHEN 3 THEN ID
END
) AS RowNum
FROM dbo.GalleryItems
WHERE ((@strSearchTerm IS NULL OR Title LIKE '%' + @strSearchTerm + '%') OR
(@strSearchTerm IS NULL OR Description LIKE '%' + @strSearchTerm + '%')) AND
(@nUserId IS NULL OR UserId = @nUserId) AND
(@strCategory IS NULL OR Category LIKE '%' + @strCategory + '%') AND
(Accepted=1)
)
SELECT *
FROM rowQueryResults
WHERE RowNum > @FirstRec AND RowNum < @LastRec
ORDER BY RowNum*@nSortSwitch

RETURN


I can set
@nSortBy
to 0, which works fine. I can set it to 3, which also works fine, but as soon as I set it to 1 or 2, it crashes with the exception
Conversion failed when converting date and/or time from character string.


What I am assuming is it's trying to convert the
Title
or
Description
to a
DateTime
for ordering. The reason why
ID
works is because it's an Integer, so that can be safely converted to DateTime (which isn't what I want it to do, obviously)

But, why would it do that? How do I make it so it does a strcmp instead of a time compare for ordering?

Answer

This has to do with data precedence, when you have a case when between different data types SQL converts the lower data types to the highest data type. Try this so your highest data type becomes varchar, so it won't try to convert Title and Description to datetime.

ORDER BY
        CASE @nSortBy
            WHEN 0 THEN convert(varchar, Date)
            WHEN 1 THEN Title
            WHEN 2 THEN Description
            WHEN 3 THEN convert(varchar, ID)
        END

https://msdn.microsoft.com/en-us/library/ms190309.aspx

Comments