order by不能使用sql变量的解决方案

　对于数据连接关系比较复杂的查询语句，通常是使用存储过程来完成分页查询的。

　　一、问题产生

　　常用的方法是通过传递几个参数，从而完成存储过程的调用。而在存储过程中，在SQL SERVER 2000中，通常是通过拼变量，然后使用EXEC执行的方法，或者采用临时表的方法处理。

　　到了2005后，SQL SERVER 提供了ROW_NUMBER() 函数方法，这给我们进行分页等需求提供了强大的支持。

　　然而，无论是在SQL SERVER 2000还是2005中，对于排序，都是不能使用变量的：

 

DECLARE @a NVARCHAR(50)
SET @a=' date DESC '

 SELECT * FROM [表名]
WHERE [条件]
ORDER BY @a

 

这是不允许的。

 

DECLARE @a NVARCHAR(50)
SET @a=' date DESC '

 SELECT * FROM [表名]
WHERE [条件]
ORDER BY CASE @a WHEN ' date DESC ' THEN  date DESC END

这样，也是不允许的。也就是说，在ORDER BY子句中，不允许使用变量！

 

　　对于绝大多数人来说，剩下来的，就是拼串了。

　　二、解决方案

　　虽然在SQL SERVER 的帮助说明中，没有说明ROW_NUMBER() 的具体用法，但通过尝试，终于找到了不使用拼串的解决办法。

　　我们现在有一张表[tab1]，我们需要对该表上的两个字段(date,price)进行排序处理，排序的方法是通过调用该存储过程的变量传递的，传递方式为：@sort='date desc'、或者@sort='date asc'方式传递给存储过程的（注：传递的参数可以更多，比如，分页所需的页行数及当前页号等）。

　　干脆，写得完整些，还传递了两个参数：@PageSize INT：页尺寸(页行数)，@PageIndex INT：当前页号。

 

 

CREATE PROCEDURE OrderTest
-- ......            //其他所需的参数
    @pageSize INT,
    @PageIndex INT,
    @Sort NVARCHAR(100)
AS

--  为了保证判断的正确性，先对传递的排序参数进行整理
SET @Sort =LOWER(REPLACE(LTRIM(RTRIM(@Sort ))))
--执行查询
;WITH MyTest AS(
    SELECT * FROM [tab1],
    CASE @sort 
        WHEN 'date desc' THEN (ROW_NUMBER() OVER(ORDER BY date desc))
  WHEN 'date asc' THEN (ROW_NUMBER() OVER(ORDER BY date asc))
  WHEN 'price desc' THEN (ROW_NUMBER() OVER(ORDER BY price desc))
  WHEN 'price asc' THEN (ROW_NUMBER() OVER(ORDER BY price asc))
  ELSE (ROW_NUMBER() OVER(ORDER BY [id])) END AS RowNumber
WHERE [各种条件]
) SELECT * FROM MyTest
    WHERE RowNumber between ((@pageindex-1)*@pagesize+1) and (@pageindex*@pagesize)
ORDER BY RowNumber

 

通过测试，“THEN”返回的参数，不带最外部的括号也一样可以达到效果（见红色标注部分）。

奇怪的是，“THEN”返回的部分，系统如何知道是何值呢？