>Hi old buddy,
>
>Cetin, what you are talking about assumes an isolation between the application developers and the DBA staff. If there is no wall between the two then it's quite simple for the client developers to ask the DBAs for whatver and they add a new SP to provide it.
>
>If there is a blind wall, then it's an entirely different discussion because then it becomes an API/Win Service/Web Service discussion which is a different argument.
>
>Personally, I have been a long-time proponent of a data services tier that isolates raw data from the client. I spoke on that subject in New Orleans in 2000 and I hope I made sense after all-night "House of the Rising Sun" sessions with Booth (lol).
>
>There are technical factors as well. SPs are precompiled at the server and re-entrant and, therefore, optimizable from the server with little or no intervention.

Frans Bouma said "Jace: Yes [precompiled] IS misleading, and I'll explain why: 'compiled' gives people who don't know the fine details the idea that a procedure is a compiled piece of code like a .exe, and any other query is interpreted. (and thus slower).

This is the main issue. Both are interpreted, namely their execution plan is interpreted. To get to the execution plan, the dyn. sql query has to be parsed, the proc is already parsed. However the next time the dyn. query is run, parsing doesn't have to occur, the execution plan is already there and is re-used.

My point against the 'compiled' argument is thus that it's not stored 'compiled' as an .exe, but in post-parse state, which is a tiny advantage, though on a query run, this is often ignorable, unless your query is really complex. "


>
>
>>Tracy,
>>Topics like this end in very long discussions anywhere on the net - actually never end:) In SQLServer 6.5 SPs might have had a very important role. But since 7.0, 2000, and finally 2005 cons look like to take over pros. I don't know if there is a clean/definite/not debatable list somewhere. Every statement of 'pros' have a counteract saying 'but is SP the definite solution'. OK let's keep it short and as just one simple question:
>>
>>How can you get the structure of table(s)/view(s) to do something in your application layer if you have only SPs provided by a DBA? Documentation only? Wouldn't that be bad enough requiring a lot of conversation with 2 separate teams.
>>I love DBAs who give me access outside of SPs. Then I'm not bothered on the number of perfect SPs she/he creates and might use as I see fit.
>>
>>Cetin
>>
>>>Please don't stop on account of silly remarks. The goal is a constructive discussion on the pros and cons of sps, correct? That way all can learn.
>>>
>>>
>>>>>>
>>>>>>perhaps an anti stored procedure person could supply a list of the cons as well.
>>>>>>
>>>>>>Nick
>>>>>
>>>>>
>>>>>LOL... I'm sure we'll here an earful (pageful?) from Walter or John shortly...
>>>>
>>>>Why? Too pity you just stopped me on my tracks showing if it was a wrong thing to do to defend counter ideas. Ok you can go on believing SPs have no cons whatsoever.
>>>>Cetin