CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Andy
Andy

Reputation: 13547

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?

Upvotes: 0

Views: 129

Answers (2)

user6638270
user6638270

Reputation:

There is a simple alternative that does not require conversion:

ORDER BY
        CASE WHEN @nSortBy = 0 THEN Date END,
        CASE WHEN @nSortBy = 1 THEN Title END,
        CASE WHEN @nSortBy = 2 THEN Description END,
        CASE WHEN @nSortBy = 3 THEN ID END

This works by a bizarre little trick. Theoretically it is doing ORDER BY over four fields in ranked order. In practice though @nSortBy can only have one value, so only one of the four ranked sorts is actually acted on.

Upvotes: 3

artm
artm

Reputation: 8584

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

Upvotes: 3

Related Questions