Row_Number pagination Sort problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Row_Number pagination Sort problem

Author

Topic

jwallz
Starting Member

14 Posts

Posted - 2008-02-16 : 19:09:03

I've been banging my head on this for hours. I can't get a case statement to work. I'm trying to create a procedure to handle dynamic paging/sorting. When i try a case statement to handle the 'order by' it doesn't work. This works:

SELECT id,recName,date,catName
FROM
(SELECT r.id,r.recName,r.date,c.catName,ROW_NUMBER() OVER
(ORDER BY recName) as RowNum
FROM recipe r INNER JOIN category c ON r.categoryID = c.id
) as rec
WHERE RowNum BETWEEN 30 and 40

but this doesn't:

Declare @sort varchar

set @sort = 'name'

SELECT id,recName,date,catName
FROM
(SELECT r.id,r.recName,r.date,c.catName,ROW_NUMBER() OVER
(ORDER BY
CASE @sort
WHEN 'name' THEN r.recName
WHEN 'date' THEN r.date
WHEN 'category' THEN c.catName
END
) as RowNum
FROM recipe r INNER JOIN category c ON r.categoryID = c.id
) as rec
WHERE RowNum BETWEEN 30 and 40

jwallz
Starting Member

14 Posts

Posted - 2008-02-16 : 19:30:59

I figured it out as soon as i posted. I wasn't setting length of variable. name was being passed as n.

Declare @sort varchar(4)