CODEWITHSUNDEEP
sqlsql-servert-sql
jlat96
jlat96

Reputation: 661

SQL Server Order By Case - Converting from Date/Time to String?

I am trying to create a stored procedure that will allow for the caller to specify if they would like to order by a column. The parameter is supposed to be optional. I am using SQL Server Express

@sortBy VARHCAR(255) = ''


BEGIN

        SELECT * FROM Document
        ORDER BY
        CASE @sortBy
            WHEN 'documentId' THEN documentId
            WHEN 'documentName' THEN documentName
            WHEN 'dateCreated' THEN dateCreated
            WHEN 'dateLastAccessed' THEN dateLastAccessed
            ELSE documentName
        END;
END

I can create the procedure, but when I execute it I get the following message

Conversion failed when converting date and/or time from character string.

dateCreated and dateLastAccessed are of type DATE, but I can't tell if they are culprit. Am I accidentally telling the DB to convert a date/time with this procedure? This is the first time that I have worked with optional parameters for stored procedures, so I may not be doing that right. Any help is appreciated

Thanks

Upvotes: 1

Views: 2162

Answers (2)

Zohar Peled
Zohar Peled

Reputation: 82524

As Tim and Sean wrote in their comment, a case expression can only return a single data type.
When each of the options contains a different data type, SQL Server tries to implicitly convert all the data types to the higher precedence data type (see the list here).

You can overcome this by using a slightly more cumbersome query:

SELECT * 
FROM Document
ORDER BY
CASE WHEN @sortBy = 'documentId' THEN documentId END,
CASE WHEN @sortBy = 'documentName' THEN documentName END,
CASE WHEN @sortBy = 'dateCreated' THEN dateCreated END,
CASE WHEN @sortBy = 'dateLastAccessed' THEN dateLastAccessed END;

Note that without specifying the else part, the case expression will return null - so that shouldn't effect the overall order of the records.

Upvotes: 6

Tomato32
Tomato32

Reputation: 2245

First, dynamic ordering doesn't work for mixed data types. That is, if you have fields of different data types that you want to order by - such as varchars and date. You need to use CONVERT to convert the mismatched data type to the same data type as the others. Or you can use dynamic sql. Maybe it's easier :))

SELECT * FROM Document
        ORDER BY
        CASE @sortBy
            WHEN 'documentId' THEN documentId
            WHEN 'documentName' THEN documentName
            WHEN 'dateCreated' THEN CONVERT(DATE, dateCreated)
            WHEN 'dateLastAccessed' THEN CONVERT(DATE, dateLastAccessed)
            ELSE documentName
        END;

Upvotes: 2

Related Questions