The rowcount description of SQL.

Set rowcount 10

Select * From tablename

= Query the top 10 records of a table.

Select @ rowcount

Is to view the current rowcount value.

,*************************************** ****************************,

The following stored procedure

-- Add
If (@ db_option_action _ = 'insert ')
Begin
Insert into sys_module (
M_applicationid,
M_parentid,
M_pagecode,
M_cname,
M_directory,
M_orderlevel,
M_issystem,
M_close
) Values (
@ M_applicationid,
@ M_parentid,
@ M_pagecode,
@ M_cname,
@ M_directory,
@ M_orderlevel,
@ M_issystem,
@ M_close
)
Set @ returnvalue = @ rowcount
End

Returnvalue is the number of records inserted for execution. Rowcount

 

/********************

 

It is said that SQL 2005 has rowid to solve the top Sorting Problem. Unfortunately, there is no chance to try it out. When writing a stored procedure in SQL 2000, top is always needed. Once top is reached, the number following top cannot be written as a variable into the pre-compiled statement, therefore, you can only construct SQL statements and execute them using exec. Without talking about efficiency, I always feel that this method is stupid.

In fact, you can use the rowcount keyword in SQL 2000 to solve this problem.

The usage of the key word rowcount is described in detail in the online help. Let's talk about the experience.

1. Use rowcount to query the results of the first few rows.

Declare @ n int
Set @ n = 1000
Set rowcount @ n
Select * From table_1

In this way, the query result is equivalent

Select Top 100 from table_1

2. In the same way, insert into... select is also valid.

Declare @ n int
Set @ n = 1000
Set rowcount @ n
Insert into table_2 (colname1)
Select colname1 = colname2 from table_1

The execution result is equivalent

Insert into table_2 (colname1)
Select Top 1000 colname1 = colname2 from table_1

3. Execute update and delete.

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 @ n int
Set @ n = 1000
Set rowcount @ n
Delete from table_1

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

Declare @ n int
Set @ n = 1000
Set rowcount @ n
Declare @ t table (id int)
Insert into @ t
Select ID from table_1 order by colname [ASC/DESC]

Delete from table_1 where ID in (select ID from @ T)

4. rowcount settings are related to sessions. If a session is occupied, the corresponding database session uses the rowcount that was last set until the session ends or the rowcount is modified.

5. You cannot use rowcount in user-defined functions.

6. Cancel rowcount.

I don't know why in the online help section, we recommend that you avoid using rowcount when writing a stored procedure. Does Ms know that the number next to top cannot be a variable? Ms may be concerned that the developer forgets to cancel rowcount and affects normal implementation.

You can use this statement to cancel rowcount.

Set rowcount 0

8. Summary

With rowcount, you can easily solve the problem that top keywords do not contain variables. In this way, you can easily solve the problem of sorting values. This avoids the use of exec to execute and construct SQL statements. It is much easier to debug the stored procedure. The only inconvenience is that the method for obtaining the current rowcount value is not found, but it is not very useful if you think about it carefully.