sam360 sam360 - 3 months ago 13
SQL Question

SQL: Limit the number of row can be queried in SQL Server

I would like to force limit the number of records being queried from the SQL Server in a way that I can report back the to user that they need to optimize their query.

Intending to use it a reporting tool where let's say the user is not supposed to extract more than 10,000 records.

SET ROWCOUNT 10000 [THROW EXCEPTION IF MORE IS SELECTED]
//user's query
SELECT ....


Is this possible to do?

Answer

I think you are asking if you can set something global that would cause an error if the result set contained more than a certain number of records.

I also think the answer to that is, "No."

However, depending on how you are constructing and executing the queries you could possible prepend SET ROWCOUNT 10001 -- note the 1 to prevent the runaway queries you want to avoid.

Then append something like @Felipe suggested:

If @@ROWCOUNT = 10001 RAISERROR ('Too many results. Please, optimize your query', 1, 1);

If you are running all queries through some sort of central processor that would be fairly easy.

EDIT:

This should demo the idea in SSMS:

SET ROWCOUNT 2
SELECT 1 UNION SELECT 2
If @@ROWCOUNT = 2 RAISERROR ('Too many results. Please, optimize your query', 1, 1)

If you are not getting an error then it is being masked by something in you code.

Comments