MS SQL用ROWCOUNT解决TOP子句不支持变量的问题 (转载)

 

在使用SQL Server（SQL Server 2000）进行排序我们经常使用的是TOP关键字，但是使用TOP有一个问题，就是TOP后面只支持数值而不支持数值型的变量，这样就没有办法在存储过程中来支持这种动态排序的要求。或者使用Exec来执行构造SQL来执行，但这样执行的效率低而且不够灵活。

实际上，在SQL Server 2000中我们完全可以使用ROWCOUNT关键字解决这个问题。

ROWCOUNT关键字作用是可以直接指定需要返回记录集的行数。

1、使用ROWCOUNT查询前100行记录。

DECLARE @rc INT

SET @rc = 100
SET ROWCOUNT @rc

SELECT * FROM emp

使用TOP可以得到同样的结果

SELECT TOP 100 FROM emp

2、在INSERT INTO..SELECT中使用ROWROUNT。

DECLARE @rc INT

SET @rc = 100
SET ROWCOUNT @rc

INSERT INTO cust (cname)
        SELECT cname=emp_name FROM emp

3、在执行UPDATE和DELETE时使用ROWCOUNT。

      因为UPDATE和DELETE无法直接使用ORDER BY语法，如果使用ROWCOUNT，将按照主键顺序从前往后操作。

DECLARE @rc INT
SET @rc    = 100

SET ROWCOUNT @rc
DELETE FROM emp

不过也有解决办法，只要能够使用ORDER BY关键字就可以了，比如说直接用含ORDER BY的子句，或者先使用ORDER BY语法把需要操作的标识列存为一个临时表或表变量，然后再操作语句中使用IN或EXISTS关键字。

DECLARE @rc INT

SET @rc = 100
SET ROWCOUNT @rc

DECLARE @tmp TABLE(ID INT)
INSERT INTO @tmp
SELECT ID FROM emp ORDER BY cid[ASC/DESC]

DELETE FROM  emp  WHERE ID IN (SELECT ID FROM @tmp )

4、对于ROWCOUNT的设置是与Session有关的。如果占用了一个Session，那么对应的这个数据库Session将使用最近一次设置的ROWCOUNT，直到Session结束或者修改了ROWCOUNT。

5、在用户自定义函数中不能使用ROWCOUNT。

6、取消ROWCOUNT设置。

使用这样的语句即可取消ROWCOUNT了，因为如果不取消之后所以的查询返回的结果集行数都会受此影响。

SET ROWCOUNT 0
我不知道为什么在联机帮助中说，写存储过程的时候应该注意尽量避免使用ROWCOUNT，而建议使用TOP。难道MS不知道TOP关键后面的数字不能为变量吗？也许MS是出于担心开发者忘记了取消ROWCOUNT而影响正常的实现。

8、总结

有了ROWCOUNT关键字后就可以非常方便的实现变量形式的排序问题了。