How to parameterize order by-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

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