how to not return data from a stored procedure

[macatter]

I'm using SQL Server 2000 and I have a stored procedure where it's possibly to return up to 20,000 rows from the select statement inside. I would like to use the TOP function to just return the top 500 rows, and if there are over 500 rows (which I'll find out from @@rowcount) then I would like to use the RETURN function to return a return-value to the calling application but also NOT return the 500 rows. I can do it this way but I don't want to: insert the data into a temp table or table datatype, do an @@Rowcount on that, then if @@Rowcount >= 500 then RETURN a different return-value, otherwise Select from the temp table so the calling application receives the data. I don't want to do that because I want to avoid the overhead of creating a temp table or table datatype, does anyone know any other way to do this? Thanks in advance.

 

[Hexonx]

How about this. It executes two selects (though one a a COUNT()), but beats a temp table.

declare @rowcount int
-- Get the rowcount of the query
select @rowcount = count(*) from ...

if @rowcount <= 500 begin
-- Less than 500, so return the records
select ...
end

-- Always return the row count for consistency
return @rowcount

 

[macatter]

Ya, I thought of that two, but I also don't want to run the select twice, you know? I'm hoping that there's some other way, but my hope is not so high...

 

[twifosp]

You're going to have to run the query twice. There is no other way to do it logically. Even in a subquery, the server will get hit twice.

Here's another way that uses less overhead though:

Use a case statement to build the query.

declare @mysql(1000)
select * from database
set @mysql =
case
when @@rowcount < 500 then 'select columns top 500 from database'
else 'select columns from database'
end

print @mysql
exec (@mysql)
return @@rowcount

 

[macatter]

Thanks for the info. You've confirmed what I thought. There's really no easy way.
Thanks again.