How to prevent returning the REsultSet in case @@ROWCOUNT > <value>?

[thelordoftherings]

Hello,

I have writted a standars Stored Procedure of Select... From... Where....
I would like to test the row count of the select query, In case it is larger than a certain value don't return the ResultSet but an error. I tried to put this after the WHERE:
IF @@ROWCOUNT > 100
RETURN (99)
But I still receive the ResultSet.
So how can I prevent this?

Roy

 

[gmmastros]

Lookup RAISERROR in books on line.

Or here...

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ra-rz_5ooi.asp

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[NoCoolHandle]

Your problem is that the result set is being returned before the test of how many rows you have.

You would have a couple of options.

1. Do a count(*) where and see if the value is greater than 100, then return with your value. or Recordset

2. Do the select into a temp table then if the @@rowcount is greater than 100 return 99 or less return the temp table.



HTH

Rob

 

[NoCoolHandle]

George, raiserror wouldn't stop the recordset as it has already been sent.

Also (and this one bugs me!!!)
If you raise and error, SQL will still probably process all the lines in the batch after the raiserror. So if you were maybe returning 10000000000 records, although the client wouldn't see them, SQL would still do the work.
Ado and dot net won't see them or the work, but if you had an insert it probably happened unless you use the "Return".

 

[gmmastros]

NoCoolHandle,

Thanks for the clarification. I will keep that in mind.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[thelordoftherings]

Hey,

I am trying to use RAISEERROR but when I call the query from my code it still returns the ResultSet, When I call it from the Query Analyser I see the error messege. Any ideas?

Roy

 

[SajidAttar]

As NoCoolHandle has suggested add check for nos of rows will be affected by your statement if it is in the range then proceed or return

something like

 declare @rowAffected int

 Select @rowAffected = Count(*) from . . . where  . . .

 if @rowAffected < 100 -- your range  
   select . . . from . . . where . . .

Regards,


"There are no secrets to success. It is the result of preparation, hard work, and learning from failure." -- Colin Powell

 

[thelordoftherings]

But why counting and selecting the same query twice?
At most cases @rowAffected will be smaller than 100...
Can't I do the select only?

Roy

 

[SajidAttar]

or you can restrict the rows resulting from your Select Statement by using SET ROWCOUNT <Exp>

  Set RowCount 100

  Select . . . From . . .  Where . . .

Regards,


"There are no secrets to success. It is the result of preparation, hard work, and learning from failure." -- Colin Powell

 

[NoCoolHandle]

Roy.

You seem to be missing the point.

Once you have built the result set and sent it to the client, it is toooooo late to raise an error. PERIOD.

That is why you will need to find out how many rows exist that match the criteia before you return it.


ie
select * from abc
--Returns a result set
print 'x'
--sends an infomessage event (the result sent went first)
raiserror ('this is tooo late',21,1)
-- the results and the x have alread been sent to the client. this just kills the process.

Rob

 

[thelordoftherings]

I see, so as far as I see it count is the best choice although I am running practically the same query twice.

Roy

 

[NoCoolHandle]

And as SajidAttar sugested.. if you don't want to check it, but want to limit the rows to below 100 you could use the rowcount option or have a top clause in your query.

select top 100 * from abc

But if for some reason your business logic specifies that any match that would return over 100 rows of detail has gone beyond what we can accept and therefore can't return any of them. You will need to find out how many rows you are sending back before you send them back.


Rob