|
|
Error Message
Server: Msg 127, Level 15, State 1, Line 1
A TOP N value may not be negative.
Causes
As the message suggests, a negative value has been passed to the TOP N clause where a positive value is expected. The TOP clause specifies that only the first set of rows will be returned from the query result. The set of rows can be either a number or a percent of the rows. The TOP expression can be used in SELECT, INSERT, UPDATE, MERGE and DELETE statements.
|
The syntax of the TOP clause is as follows:
TOP (expression) [PERCENT] [ WITH TIES ]
The “expression” in the TOP clause is the numeric expression that specifies the number of rows to be returned. If PERCENT is specified, this value is implicitly converted to a FLOAT value; otherwise, it is converted to BIGINT.
This error message can easily be generated using the following SELECT statement:
SELECT TOP (-10) * FROM [dbo].[Customers]
Msg 127, Level 15, State 1, Line 1
A TOP N value may not be negative.
Novice SQL developers might think that if a negative value is passed to the TOP clause in a statement, the rows returned will be the bottom set of rows.
A possible scenario where this error can be encountered is when the number of rows specified in the TOP clause is a local variable and the value is computed on the fly based on the number of rows in the table. Here’s a sample script:
DECLARE @RowCount INT
DECLARE @TopCount INT
SELECT @RowCount = COUNT(*) FROM [dbo].[Customers]
SET @TopCount = @RowCount * 0.10 - 10
SELECT TOP (@TopCount) * FROM [dbo].[Customers]
This script returns 10 rows less than 10% of the total number of rows in the [dbo].[Customers] table. This error can be encountered if the number of records in the [dbo].[Customers] table is less than 100. As an example, if the number of rows in the [dbo].[Customers] table is only 90, 10% of part will be 9 and subtracting 10 from this will be -1.
Another possible scenario when this error can be generated is in a stored procedure and the number of rows returned by the stored procedure is passed as a parameter, as can be seen in the following script:
CREATE PROCEDURE [dbo].[usp_SearchCustomer]
@SearchString VARCHAR(20),
@ResultCount INT
AS
SELECT TOP ( @ResultCount ) *
FROM [dbo].[Customers]
WHERE [FirstName] LIKE '%' + @SearchString + '%' OR
[LastName] LIKE '%' + @SearchString + '%'
GO
EXECUTE [dbo].[usp_SearchCustomer] 'Mickey', -10
Msg 127, Level 15, State 1, Procedure usp_SearchCustomer, Line 5
A TOP N value may not be negative.
This stored procedure performs a search in the [dbo].[Customers] table where either the first name or last name matches the search string. Aside from the search string, the number of records to be returned is also passed as a parameter to the stored procedure. If this particular stored procedure is called from a client application and the number of rows to be returned by the stored procedure is not validated to make sure that it is always positive, this error message can easily be generated.
Solution / Work Around:
The easiest way to avoid this error is to make sure that a positive number is specified in the TOP clause. In the first example earlier, simply change the negative value to a positive number to return that number of rows from the table:
SELECT TOP (10) * FROM [dbo].[Customers]
If the purpose of specifying the negative value in the TOP clause is to return the bottom number of rows from a table, simply specify an ORDER BY clause in the SELECT statement and order the result in a descending order:
SELECT TOP (10) * FROM [dbo].[Customers] ORDER BY [CustomerID] DESC
In the case of the second example where the value specified in the TOP clause comes from a local variable, simply add a condition to the script to check the value of the local variable and if it is negative, set it to a default positive number. One way to do this is by adding an IF condition to the script:
DECLARE @RowCount INT
DECLARE @TopCount INT
SELECT @RowCount = COUNT(*) FROM [dbo].[Customers]
SET @TopCount = @RowCount * 0.10 - 10
IF @TopCount < 0
SET @TopCount = 0
SELECT TOP (@TopCount) * FROM [dbo].[Customers]
Alternatively, instead of an IF condition, the CASE function can be used directly inside the TOP clause expression, as shown in the following script:
DECLARE @RowCount INT
DECLARE @TopCount INT
SELECT @RowCount = COUNT(*) FROM [dbo].[Customers]
SET @TopCount = @RowCount * 0.10 - 10
SELECT TOP (CASE WHEN @TopCount < 0 THEN 20 ELSE @TopCount END) * FROM [dbo].[Customers]
In this case, if the number of rows becomes negative, the top 20 rows are returned inside.
Lastly, in the case of the stored procedure, to be sure that this error will not be encountered, using the second alternative in the earlier example, the number of rows specified in the parameter can be checked using the CASE function and if it is a negative number, simply return the top 20 rows from the table:
CREATE PROCEDURE [dbo].[usp_SearchCustomer]
@SearchString VARCHAR(20),
@ResultCount INT
AS
SELECT TOP ( CASE WHEN @ResultCount < 0 THEN 20 ELSE @ResultCount END) *
FROM [dbo].[Customers]
WHERE [FirstName] LIKE '%' + @SearchString + '%' OR
[LastName] LIKE '%' + @SearchString + '%'
GO
Passing a negative value to the @ResultCount parameter will return the top 20 customers in the table that match the search string based on the first name or last name:
EXECUTE [dbo].[usp_SearchCustomer] 'Mickey', -10
|