row number over text column sort
- by Marty Trenouth
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
