>If you read the SQL Server 7.0 what's new, you will see that plans are cached for standard queries and reused. As a matter of fact, plans are reused for non exact queries. So, according to the docs, the speed a SP has over an ad hoc query is about negligable.
>
>SQL statements submitted through the ODBC SQLPrepare function and the OLE DB ICommandPrepare interface can also share plans. Simple ad hoc plans are shared as well. Prepared and ad hoc plans reuse is supported further by passing parameter markers all the way through to the database.
>

In some cases, the plan can be shared, if the query is almost identical to a prior query.

>>

SPs allow you to hide the implementation from the interface. If the schema for the database changes, the SP can be changed without having to recode application logic.
>>
>
>Isn't this also true of remote views?

Yes. To a degree.

>Isn't changing a SP = recoding applicaiton logic?

Again, yes. But on a much smaller scale.

------------------------------------------------
John Koziol, ex-MVP, ex-MS, ex-FoxTeam. Just call me "X"
"When the going gets weird, the weird turn pro" - Hunter Thompson (Gonzo) RIP 2/19/05