Select variable number of rows with SQL

Reynold Greenlaw

In SQL Server 2005, you can now use a variable when getting the first n number of rows in a SELECT query:

DECLARE @NumberOfRowsIWant INT
 SET @NumberOfRowsIWant = 23

SELECT TOP (@NumberOfRowsIWant) *
 FROM T_Client

The OUTPUT clause added in SQL 2005 gives you access to the inserted and deleted tables, normally only useable in a trigger, in your stored procedures (SP). Combine the clause with an UPDATE or a DELETE and you can, for example, select fields from the rows inserted into a temporary table for later use. A slight drawback is that triggers on the tables inserted / updated / deleted could cause values in these tables in your SP not to be available.

See some examples of projects that OCC have worked on using MySQL and SQL Server.