RowCount and @ @rowcount in SQL

1 usage of rowcount:2 
3 The function of rowcount is to restrict the subsequent SQL to stop processing after returning the specified number of rows, such as the following example,4 Set RowCount Ten
5 Select *  fromTable A6 
7Such a query will only return the first 10 data in table A. It and the "Select Top Ten *  fromThe role of Table a ". Note thatSet RowCountsettings are valid throughout the session. such as the following SQL example:8 Set RowCount Ten
9 Select *  fromTable ATen Go
 One Select *  fromTable B A 
 - both table A and Table B return only the first 10 data.  -To cancel the qualification of SET ROWCOUNT, simply set theSet RowCount 0you can do it.  the 
 - from the above example, it seems that rowcount does not have much use, limit the data of the query results, we use top to be able to, and do not have to worry about if forget to cancel the rowcount's setting and the effect on the subsequent SQL.
But in the following circumstances, rowcount's settings will bring us a lot of convenience oh.  - 
 -We all know that selectTopYou cannot add arguments later, you can only use a specific number of type int. If we want to implement the function of the parameter in the back of top, we only construct the SQL string and then use exec to execute it. For example: + Declare @n int
 - Declare @sql nvarchar( +)
 + Set @n=Ten
 A Set @sql='Select Top'+cast(@n  as varchar(Ten))+'* FROM Table a'
 at exec(@sql)
 - 
 - not to mention the performance of exec in the above statement, from the readability of SQL is very unfriendly. But if we use rowcount to solve this, it will be very elegant, because the SET rowcount can use parameters later. Examples are as follows: - Declare @n int
 - Set @n=Ten
 - Set RowCount @n
 in Select *  fromTable A - 
 toAttention:SetThe limit of rowcount is as valid as the modification and deletion. such as the following example: + Set RowCount Ten
 - UpdateTable ASetQty=Ten whereId< -
 the 
 *In this way, the above statement will only modify the ID in table a<100 of the first 10 data (assuming ID<100 more data than ten) $ 
Panax Notoginseng Delete is the same - Set RowCount Ten
 the Delete  fromTable A + 
 A In this way, the above statement will only delete the first 10 data in table A.  the 
 + How to use @ @Rowcount
 - 
 $ @ @Rowcount looks much like rowcount, only two different @, but their function is not the same,@ @Rowcount Primarily returns the number of rows of data that were affected by the last SQL statement, such as: $ Select Top 2 *  fromTable A - Select @ @Rowcount
 - 
 theIf the amount of data in table A is greater than or equal to 2, then select@ @Rowcount will return 2, if there are only 1 or 0 data, then select@ @Rowcount will return 1 or 0.  - 
WuyiNote, do not put@ @Rowcount understood to return only the number of results for a query, delete, modify, add, and so on, will return correctly.@ @Rowcount Value. For example: the UpdateTable ASetGid='a' whereGid='a'
 - Select @ @Rowcount
 Wu 
 -If a GID exists in table a='a'of data, then select@ @Rowcount Returns the number of rows for which the data was modified, if there is no GID='a'of data, then select@ @Rowcount will return 0, delete and new are the same.  About 
 $So, where are we going to use@ @Rowcount??  -One, maybe we see@ @Rowcount Figure Most of the place is in the trigger, a good trigger is usually preceded by the IF@ @rowcount=0return statement, such as: - Create TriggerTi_tablea onTableA afterUpdate
 -  as
 A if @ @rowcount=0 return
 + ... the 
 - Thus, if the number of data rows modified by TableA is 0, then the trigger Ti_tablea exits without executing the subsequent code.  $ 
 theTwo, the second possible use of the place is that we can use@ @rowcount for recursion or looping. such as the following example: the Declare @n int
 the Set @n=1
 the Select *  fromClient_goodswhereId=@n
 - 
 in  while @ @rowcount>0
 the begin
 the Set @n=@n+1
 About Select *  fromClient_goodswhereId=@n
 the End
 the 
 theThis example is to query for the ID in client_goods first=1 of the data, if any, then query whether there is an ID=2 of the data, keep checking until the ID is not contiguous. Of course, when you look at this example, don't think about the meaning of this example, it's just that@ @rowcount can be used as a loop condition

RowCount in SQL with @ @rowcount