Avoid returning results under certain conditions

Question

I am executing a select statement. If the number of rows in the result set is less than or equal to a certain number, I return the selected rows. If the number of rows is greater than a certain number, I do not want to return rows.

After making a selection and performing a comparison, if the number of rows is greater than the number of valid rows, I do:

SELECT TOP 0 NULL AS ID 

I found that both records that were originally selected also come back with a second set of results with one column named ID and no records. Obviously, the original select statement is still coming back, and I want to avoid this. Is there any way?

EDIT: Forgot to add that I need to return a status value that indicates whether there were more rows than were allowed. This means that I should get a counter and would rather have an account without having to execute the same query twice. Therefore, if the maximum number of allowed lines is 25, but actually there are zero lines, then I return the status 0. But if the number of lines is more than 25, then I do not return any lines, but I set the status to 1.

Answer 1

Make a selection in the temp table, check the number of rows in the table and use the temp table in your selections or return 0 rows.

 -- Your query goes here select ID into #T from YourTable --where -- Check the number of rows returned if (select count(*) from #T) <= 10 begin -- Return the data select ID from #T end else begin -- Don't return anything select ID from #T where 0 = 1 end drop table #T 

You can also do this in a single query with count(*) over() .

 select ID from ( select ID, count(*) over() as C from YourTable --where ) T where C <= 10 

Or using CTE

 with C as ( select ID from YourTable --where ) select ID from C where (select count(*) from C) <= 10 

Choose everything that best suits your needs or best fulfills your data.

Update
A modified version of the temp table that returns the number of rows.

 declare @MaxRows int set @MaxRows = 25 declare @ActualRowCount int select top(@MaxRows+1) ID into #T from YourTable set @ActualRowCount = @@rowcount if @ActualRowCount = 0 begin -- No rows returned select null as ID, 0 as [RowCount] end else if @ActualRowCount > @MaxRows begin -- Too many rows returned select null as ID, -1 as [RowCount] end else begin -- Return rows from temp table select ID, @ActualRowCount as [RowCount] from #T end drop table #T 

Answer 2

I assume that you are using SQL Server to use @@ROWCOUNT (Returns the number of rows affected by the last statement.)

So here is an example:

 SELECT ID FROM [YourTable] WHERE [Your Conditions] IF @@ROWCOUNT > [YourLimit] SELECT ID FROM [YourTable] WHERE 0=1 // return empty records ELSE SELECT ID FROM [YourTable] WHERE [Your Conditions] 

What all

Answer 3

Load the string count parameters into variables and then SELECT :

 DECLARE @rows INT; DECLARE @maxRows INT = 1000; --set your desired max value here SELECT @rows=COUNT(1) FROM [myTable]; SELECT ID FROM [myTable] WHERE @rows <= @maxRows; 

You will either get an empty set if there are too many rows, or a result set