How to parameterize order by

Hoang Minh 0
Viewed 45

I have a simple SQL script, and I want to parameterize the sort by:

CREATE OR ALTER PROCEDURE dbo.GetLog
    @SortDirection NVARCHAR(4),
    @SortBy NVARCHAR(100)
AS
BEGIN
    SELECT Id 
    FROM dbo.TestLog
    ORDER BY
        CASE WHEN @SortDirection = 'asc' 
            THEN
                CASE
                    WHEN @SortBy = 'Id' THEN Id -- Id is an INT
                    WHEN @SortBy = 'IsDeleted' THEN Deleted -- Deleted is a BIT
                    WHEN @SortBy = 'Description' THEN Description -- Description is a NVARCHAR
                END
        END ASC,
        CASE 
            WHEN @SortDirection = 'desc' 
                THEN
                    CASE
                        WHEN @SortBy = 'Id' THEN Id -- Id is an INT
                        WHEN @SortBy = 'IsDeleted' THEN Deleted --  Deleted is a BIT
                        WHEN @SortBy = 'Description' THEN Description -- Description is a NVARCHAR
                    END
        END DESC

If @SortBy = 'Id', everything works fine. However, if @SortBy is anything else, then an exception will be thrown:

Conversion failed when converting the varchar value '12A' to data type bit

Is there anyway to fix this issue? Thank you.

sql-server sql
2 Answers

For optimal performance use dynamic SQL. The CASE in ORDER BY will prevent using an index. eg

CREATE OR ALTER PROC dbo.GetLog
@SortDirection NVARCHAR(4),
@SortBy NVARCHAR(100)
AS
BEGIN
    declare @sql nvarchar(max) = concat(N'
    Select Id from dbo.TestLog
    Order BY ', quotename(@SortBy), ' ', CASE When @SortDirection = 'desc' THEN 'desc' else 'asc' end)
    
    --print ( @sql )
    exec sp_executesql @sql
END
go
David Browne - Microsoft0

The solution is to case your first case condition to SQL_VARIANT, and every after it will works accordingly. All thanks to Martin Smith's answer.

CREATE OR ALTER PROC dbo.GetLog
@SortDirection NVARCHAR(4),
@SortBy NVARCHAR(100)
AS
BEGIN
Select Id from dbo.TestLog
Order BY
    CASE When @SortDirection = 'asc' THEN
        CASE
            WHEN @SortBy = 'Id' THEN CAST(Id AS SQL_VARIANT)
            WHEN @SortBy = 'IsDeleted' THEN Deleted
            WHEN @SortBy = 'Description' THEN Description
       END
    END ASC,
    CASE When @SortDirection = 'desc' THEN
        CASE
            WHEN @SortBy = 'Id' THEN CAST(Id AS SQL_VARIANT)
            WHEN @SortBy = 'IsDeleted' THEN Deleted
            WHEN @SortBy = 'Description' THEN Description
       END
    END DESC
Hoang Minh0
Related