SELECT row_number() over (order by @order_by) passes synta..

/*
Using variables for dynamic sorting

SELECT row_number() over (order by @order_by) executes without an error but
doesn't do anything. Is this a bug or am I doing something wrong?

Using SQL Server 2005 SP2
*/


if object_id('tempdb.dbo.#tRowNumber') is not null
drop table #tRowNumber
go
create table #tRowNumber (id int identity, account_number char(10), name
varchar(100))
go
insert #tRowNumber values ('0000000000','NameZero')
insert #tRowNumber values ('1111111111','NameOne')
go


declare @order_by sysname
set @order_by = 'name'

SELECT row_number() over (order by @order_by) as row_number
, id
, account_number
, name
from #tRowNumber


-----------
row_number id account_number name
1 1 0000000000 NameZero
2 2 1111111111 NameOne

The variable, in this case, is being interpreted as a string literal.
You'll have to use dynamic SQL if you want to actually change the sort order
dynamically...


--

Adam Machanic
SQL Server MVP - http://sqlblog.com

Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220



<a> wrote in message
> /*
> Using variables for dynamic sorting
>
> SELECT row_number() over (order by @order_by) executes without an error
> but doesn't do anything. Is this a bug or am I doing something wrong?
>
> Using SQL Server 2005 SP2
> */
>
>
> if object_id('tempdb.dbo.#tRowNumber') is not null
> drop table #tRowNumber
> go
> create table #tRowNumber (id int identity, account_number char(10), name
> varchar(100))
> go
> insert #tRowNumber values ('0000000000','NameZero')
> insert #tRowNumber values ('1111111111','NameOne')
> go
>
>
> declare @order_by sysname
> set @order_by = 'name'
>
> SELECT row_number() over (order by @order_by) as row_number
> , id
> , account_number
> , name
> from #tRowNumber
>
>
> -----------
> row_number id account_number name
> 1 1 0000000000 NameZero
> 2 2 1111111111 NameOne
>