How to parameterize order by in sql server?-CodePudding

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

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 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：

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

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