我想根据 @SortDir 我正在传递给sql server的参数。但我有个错误：
消息156，级别15，状态1，行62
关键字“asc”附近的语法不正确
这是我的动态sql。我正在对列执行动态搜索，但无法对其进行排序

ALTER PROCEDURE Dsearchsor
    @Name nvarchar(20),
    @Description nvarchar(20),
    @StartIndex int,
    @PageSize int,
    @SortDir nvarchar(20),
    @SortField nvarchar(max)
AS
BEGIN
    DECLARE @SQL nvarchar(max) = N''
    DECLARE @Select nvarchar(max) = N'
    ;With data AS (Select P.Id,
    P.Name,
    P.Description,
    P.Price,
    P.Prorate,
    B.Name as BillingPeriod,
    P.CreatedDate, 
        CASE '+@SortField+'
                WHEN P.Name THEN ROW_NUMBER() OVER (ORDER BY P.Name)
            END rn '

    DECLARE @From NVARCHAR(MAX) = N'From Products P '
    DECLARE @WHERE NVARCHAR(MAX) = N''

    IF @Name IS NOT NULL  
    BEGIN
        SET @WHERE = @WHERE + 'Where P.Name LIKE ''' +'%'+@Name+'%' + ''''
    END

    IF @Description IS NOT NULL
    BEGIN
        IF LEN (@Where) > 0 
        BEGIN
            SET @Where = @Where + ' AND '       
        END

        SET @Where = @Where + 'Description LIKE ''' + +'%'+@Description+'%' + '''' +' )'
    END

    SET @From = @From + ' inner join BillingPeriods B on P.BillingPeriodId = B.Id '

    SET @SQL = @Select + @From + @Where +' 
    Select *,(SELECT COUNT(1) FROM data) TotalCount From data                    
    order by CASE WHEN '+ @SortDir +' = ASC' +' THEN rn ELSE -rn END  ******This Line Gives Error******
    OFFSET '+(CAST(@StartIndex as varchar(10)))+' ROWS FETCH NEXT '+(CAST(@PageSize as varchar(10)))+' 
    ROWS ONLY'

    EXECUTE sp_executesql @SQL
END

帮我个忙，我对sql还很陌生。还是有更好的办法？如果是，请告诉我。