is it possible to close recordsets within a procedure??

[kakmouth]

hi.

just wanted to see if it was possible to close a recordset from within the procedure that created it to avoid returning unneeded recordsets??? say for instance i create a recordset merely to see if a record exists for a given filter but would no longer need that recordset, is it possible to close that recordset so that it is not returned??? it seems that recordsets are created for just about any action executed within a procedure, most of which may not be necessary...

any help would be appreciated.
thanks, mike.

 

[bperry]

You might try something like this. Here, the 'result' of the query is set equal to a variable, not returned as a recordset.

declare @myCount int
set @myCount = 0
Select @myCount = (Select Count(*) from Customers where IdNum= 203)
if @myCount > 0
begin
Do Some Processing
end
else
Begin
Do some different processing
End
-----------------

 

[tlbroadbent]

A more efficient method of testing for the existence of a record set is the IF EXISTS statement.

IF EXISTS
(Select * From table Where <insert your criteria>)
Begin
<insert your code>
End

IF EXISTS is very efficient because it returns TRUE as soon as the first row is found. You can use JOINs and all manner of criteria in the query so this can be a very powerful tool. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[bperry]

Mike:
My apologies, I should have replied more directly to your question....
As far as I know, it is not possible to 'close' the recordsets. (Anyone: am I wrong here?) So, what you have to do is find ways to 'not create' a record set, like my example that I have posted. This is generally quite easy to do, so that it's rather unusual to be forced to create recordsets that you don;t really want.

Hope this helped a bit,

bp

 

[kakmouth]

yeah, you both helped quite a bit... very good suggestions. thanks.

 

[bperry]

Terry:
I like the example of the EXISTS test. That's a good thing for folks to keep in mind.

I agree that IF EXISTS is well-known to be quite efficient. However, I have been thinking for some time that in the case of COUNTing a single customer, where at most one record can be returned (i.e. either 0 or 1), then in that specific situation both of our examples should perform the same. Agree?
bp

 

[kakmouth]

one more question....

what if that query statement has to be dynamic within this procedure -- so that normally what i would do is create a string variable holding the text of the statement... and then EXECUTE() that statement:

*******
SET @lsSQL = &quot; select * from customers where name = '&quot;+@lsName+&quot;'&quot;

EXECUTE(@lsSQL)
*******

this code would create a recordset... how could i create a dynamic query statement and use the &quot;IF EXISTS&quot; test or something similar???

thanks again, mike.