Re: Effects on RST in Calling Proc

David W. Fenton wrote:

Obviously, the snapshot avoids that, but I think it's better to code
the original procedure in a way that is likely to survive the
largest number of revisions without needing to be altered, and to
me, storing the PK value has that advantage over either alternative
that is dependent on the state of the recordset, rather than derived
from the underlying data. 

Thanks for the feedback.

In the original proc, the recordset is snapshot and is passed to the called proc so that another field can be searched for a value that is being tested in the calling proc. So the nature of the two together is such that the recordset will not be changed.

I think you're right, the PK find first alternative is probably better, though. But as I said, the nature of my procedures in this case are such that changes made to the recordset in the second procedure would not be appropriate. I suppose the two could have been melded into one procedure, but I thought the second could lend itself later in development to other procs similar to the one here that calls it.

I try as much as possible to avoid data manipulation by row processing and prefer set processing, ie, I prefer to execute an update or insert statement as opposed to going through each row in a recordset and doing edit/update, so I generally stick with dbopensnapshot.

--
Tim    http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto  "TIM-MAY!!" - Me
.

Relevant Pages

  • Re: how to retrieve [RecordsAffected] count from cmd.Excecute in V
    ... the original j a variant was because Longs and Integers by default are 0. ... And here is the related ADO code to invoke the Proc and get the "Records ... assigning the result to a Recordset object) try removing the SELECT ...
    (microsoft.public.data.ado)
  • How to get past an error?
    ... sql error but still need to return a recordset or print statement that ... statemets and depending on the type of error, might see the recordset.. ... in batchs.. ... PS try to make this proc send back the error,print and get the record ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Determining column names and types of recordset returned by stored procedure
    ... then wraps the call to the proc with SET NO_BROWSETABLE and SET FMTONLY ... the code above actually returns one empty recordset for each ... Visual Studio would only show TextResults as a resulting ... >> Studio 2003 Server explorer is reliably providing the information ...
    (microsoft.public.sqlserver.programming)
  • Re: Effects on RST in Calling Proc
    ... >> Obviously, the snapshot avoids that, but I think it's better to ... the recordset is snapshot and is passed to ... > the called proc so that another field can be searched for a value ... you avoid the issue entirely. ...
    (comp.databases.ms-access)