Avoid returning resultset under certain conditions

Question

I perform a select statement. If the number of rows in the resultset 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 want to return no rows.

After running the select and doing the comparison, if the number of rows was greater than the number of rows allowed, I execute:

SELECT TOP 0 NULL AS ID

What I discovered was that both the records that were initially selected are also returned along with a second result set with a single column called ID and no records. Obviously, the initial select statement is still being returned 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 have to get at the count and would prefer to have the count without having to run the same query twice. So if the maximum number of rows allowed is 25 but zero rows actually exist, then I return a status of 0. But if the number of rows is greater than 25, then I don't return any rows but I set the status to -1.

Answer 1

Do the select into a temp table, check the number of rows in the table and use the temp table in your select 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 it in one query using count(*) over().

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

Or with a CTE

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

Choose whatever best fits your needs or performs the best with your data.

Update
A modified temp table version that returns the row count.

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 you are using SQL Server so you can use the @@ROWCOUNT (Returns the number of rows affected by the last statement. )

So here 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]

That's all

Answer 3

Load your row 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'll either get an empty set when there are too many rows, or the result set