Re: Data binding - question

Idiot wrote: > > As long as we are on the subject, > > A function gets called about 20,000 times a day, uses same select > statements over and over with different value and this is how I have > implemented it. > > static int x = FALSE, counter = 0; > > func () > { > if ( !x ) > PREPARE > DECLARE > x = TRUE; > > if ( counter%50 == 0) > OPEN > else > OPEN WITH REOPTIMIZATION > FETCH > counter++ > } > > Am I on the right track? I do not CLOSE cursor assuming that next OPEN will > re-open the cursor. What is the overhead of not freeing the statement or > cursor? The function is part of a 24x7 server. It is always a good idea to close the CURSOR just do not free the cursor or the statement. Also if your open gets a -710 error you need to re-PREPARE the statement because the query plan has become invalid (usually because someone altered the table or an update statistics has drastically changed the query plan).