Ms SQL uses rowcount to solve the problem that top clause does not support Variables

When SQL Server (SQL Server 2000) is used for sorting, we often use top keywords. However, if top is used, only numeric variables are supported but not numeric variables are supported, in this way, there is no way to support this dynamic sorting requirement in the stored procedure. You can also use exec to construct SQL statements for execution, but the execution efficiency is low and not flexible enough.

In fact, we can use SQL Server 2000RowcountKeyword to solve this problem.

The rowcount keyword specifies the number of rows in the record set to be returned.

1. UseRowcountQuery the first 100 rows of records.

Declare @ RC int Set @ rc = 100 Set rowcount @ RC Select * from EMP

Use top to get the same result.

Select Top 100 from EMP

2. Use in insert into... selectRowrount.

Declare @ RC int Set @ rc = 100 Set rowcount @ RC Insert into Cust (cname) Select cname = emp_name from EMP

3. UseRowcount.

Because the order by syntax cannot be directly used for update and delete, if rowcount is used, the Operation will follow the primary key sequence before and after.

Declare @ RC int Set @ rc = 100 Set rowcount @ RC Delete from EMP

However, there are also solutions, as long as the order by keyword can be used, for example, directly using a clause containing order, alternatively, you can use the order by syntax to store the ID column to be operated as a temporary table or table variable, and then use the in or exists keyword in the operation statement.

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.RowcountIs related to the session. If a session is occupied, the corresponding database session will useRowcountUntil the session ends or is modifiedRowcount.

5. cannot be used in user-defined functionsRowcount.

6. Cancel the rowcount setting.

You can use this statement to cancel rowcount, because the number of rows returned by the query will be affected if it is not canceled.

Set rowcount 0

I don't know why I should avoid using it whenever possible when writing a stored procedure in online help.RowcountAnd top is recommended. Does Ms know that the number next to the top key cannot be a variable? Maybe Ms is out of fear that the developer forgot to cancelRowcountThis affects normal implementation.

8. Summary

WithRowcountAfter the keyword, you can easily sort the variables.

For details, refer:Http://www.livebaby.cn/blog/u/meil/archives/2008/sqlserver-2000-rowcount.html