row number over text column sort

Question

I'm having problems with dynamic sorting using ROW Number in SQL Server. I have it working but it's throwing errors on non numeric fields. What do I need to change to get sorts with Alpha Working???

ID  Description
5   Test    
6   Desert  
3   A evil  

Ive got a Sql Prodcedure

CREATE PROCEDURE [CRUDS].[MyTable_Search]
    -- Add the parameters for the stored procedure here
    -- Full Parameter List  
    @ID int = NULL,     
    @Description nvarchar(256) = NULL,  
    @StartIndex int = 0,
    @Count int = null,
    @Order varchar(128) = 'ID asc'
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
Select * from
    (
        Select ROW_NUMBER() OVER 
            (Order By
                case 
                    when @Order = 'ID asc' then [TableName].ID  
                    when @Order = 'Description asc' then [TableName].Description    
                end asc,
                case
                    when @Order = 'ID desc' then [TableName].ID 
                    when @Order = 'Description desc' then [TableName].Description   
                end desc
            ) as row,
            [TableName].* from [TableName]
        where 
            (@ID IS NULL OR [TableName].ID = @ID)  AND
            (@Description IS NULL OR [TableName].Description = @Description)
) as a
where 
    row > @StartIndex  
    and (@Count is null or row <= @StartIndex + @Count)
 order by
    case 
        when @Order = 'ID asc' then a.ID    
        when @Order = 'Description asc' then a.Description  
    end asc,
    case
        when @Order = 'ID desc' then a.ID   
        when @Order = 'Description desc' then a.Description 
    end desc


END

Answer 1

it works fine for me:

declare @TableName table (id int,Description varchar(50))
insert @TableName values (1,'aaa') 
insert @TableName values (2,'bbb') 
insert @TableName values (3,'ccc') 
insert @TableName values (4,'ddd') 
insert @TableName values (5,'eee') 
insert @TableName values (6,'fff') 
insert @TableName values (7,'ggg') 
insert @TableName values (8,'hhh') 
DECLARE @Order         varchar(10)
       ,@ID            int
       ,@Description   varchar(50)
       ,@StartIndex    int
       ,@Count         int

SELECT @Order='Description desc'
      ,@StartIndex=2
      ,@Count=3

--query unchanged, except alias to "t" and table name to "@TableName"
Select * from
    (
        Select ROW_NUMBER() OVER 
            (Order By
                case 
                    when @Order = 'ID asc' then t.ID  
                    when @Order = 'Description asc' then t.Description    
                end asc,
                case
                    when @Order = 'ID desc' then t.ID 
                    when @Order = 'Description desc' then t.Description   
                end desc
            ) as row,
            t.* from @TableName  t
        where 
            (@ID IS NULL OR t.ID = @ID)  AND
            (@Description IS NULL OR t.Description = @Description)
) as a
where 
    row > @StartIndex  
    and (@Count is null or row <= @StartIndex + @Count)
 order by
    case 
        when @Order = 'ID asc' then a.ID    
        when @Order = 'Description asc' then a.Description  
    end asc,
    case
        when @Order = 'ID desc' then a.ID   
        when @Order = 'Description desc' then a.Description 
    end desc

output:

row                  id          Description
-------------------- ----------- -------------
3                    3           ccc
4                    4           ddd
5                    5           eee

(3 row(s) affected)

possibly post more details about the data you are running this with and the actual error message.

EDIT based on OP's comment, try this:

declare @TableName table (id int,Description varchar(50))
insert @TableName values (1,'1') 
insert @TableName values (2,'bbb') 
insert @TableName values (3,'ccc') 
insert @TableName values (4,'ddd') 
insert @TableName values (5,'eee') 
insert @TableName values (6,'fff') 
insert @TableName values (7,'ggg') 
insert @TableName values (8,'hhh') 
DECLARE @Order         varchar(50)
       ,@ID            int
       ,@Description   varchar(50)
       ,@StartIndex    int
       ,@Count         int

SELECT @Order='Description desc'
      ,@StartIndex=2
      ,@Count=3

Select * from
    (
        Select ROW_NUMBER() OVER 
            (Order By
                case 
                    when @Order = 'ID asc' then RIGHT(REPLICATE('0',10)+CONVERT(varchar(10),t.ID),10)
                    when @Order = 'Description asc' then t.Description    
                end asc,
                case
                    when @Order = 'ID desc' then RIGHT(REPLICATE('0',10)+CONVERT(varchar(10),t.ID),10)
                    when @Order = 'Description desc' then t.Description   
                end desc
            ) as row,
            t.* from @TableName  t
        where 
            (@ID IS NULL OR t.ID = @ID)  AND
            (@Description IS NULL OR t.Description = @Description)
) as a
where 
    row > @StartIndex  
    and (@Count is null or row <= @StartIndex + @Count)
 order by
    case 
        when @Order = 'ID asc' then RIGHT(REPLICATE('0',10)+CONVERT(varchar(10),a.ID),10)
        when @Order = 'Description asc' then a.Description  
    end asc,
    case
        when @Order = 'ID desc' then RIGHT(REPLICATE('0',10)+CONVERT(varchar(10),a.ID),10)
        when @Order = 'Description desc' then a.Description 
    end desc

I basically convert the IDs to strings using this logic:

RIGHT(REPLICATE('0',10)+CONVERT(varchar(10),ID),10)