>Walter in taht example you said some will say use an SP other use SPT, that specific example I would use Regular ADO.NET or VFP to SQL cursors, start a transaction and then submit all the inserts updates, deletes on a go.

Yep, and how would you do it with SPs??

So which one is more flexible?

>But that isn't the example i'm talking about, I usually use SP's other then when performing complex queries using tempoary tables and at the end will generate usually a cursor that's what get returned to the client, that's when SP's make more sense to me and when they will beat SPT, not cause SP's are always fast than SPT but cause ot the latency roundtrips between the server and the client.
>Hope this clarify more what I was saying, if not I can always try to explain to you in Portuguese. ;)

Ah, a total different case, really not having to do with transactions does it?

I do have a few of those SPs. One actualy has a 400 line SQL SELECT in it. The reason I have a SP in there is the additional time it takes to pass the query in the first place. However, this only makes sense if the execution time is very short. For longer batches you can just pass the whole contents of the SP through SPT. Just the same number of roundtrips.

Again there is not so much difference between SPs and parameterized queries or batches thorugh SPT. As for temporary tables, painfull creating them as you can't create them with parameterized queries (the get out of scope), so you have to do it with pure dynamic SQL. But of course once created, they stay there until you drop them or the session ends.

Walter,





>>>Alexandre,
>>>
>>>>Walter you can but as John said this is when the use of SP's make the more sense, remember that multiple round trips have a speed cost, where nothing is processing is just the delay of comunication between the server and the client while if u call a SP that encapsulate all that same call's in just 1 unit it will be faster then SPT, and this is a fact that u can mesure.
>>
>>>Huh?? Lets say that you have an order with orderlines. And you want to submit the thing into one transaction. One way or another the information has to be send to the server.
>>
>>BTW, you can pass all the needed SQL code through SPT as well, no problemo. No more rountrips.
>>
>>>How do you want to do this with a SP?? An example please, I'm curious how you guys think it would be easier and at the same time performing better than SPT .....
>>>
>>>If you are talking about the extra SQLCOMMIT(), I don't think anyone would argue this is measurable within a whole transaction.
>>>
>>>And even if you've got a case where a SP does perform better and is more logical, my reply was based on the fact that mission critical transactions spanning multiple tables ARE POSSIBLE with SPT. So the argument that an SP has the advantage of wrapping everything into a transaction, is simply not true.
>>>
>>>Walter,