Posted by Erland Sommarskog on 11/03/05 10:43

Martijn Tonies (m.tonies@upscene-removethis.nospam.com) writes:
>> That's not really how you call extended stored procedure. But you could
>> encapsulate the XP in a user-defined function to get this syntax.
>> However, there is a big overhead for calling a UDF in a WHERE clause in
>> SQL 2000 (this overhead has been reduced in SQL 2005). If you then add
>> a call to extended stored procedure that gives you context switches and
>> all, it's getting really bad.
>
> Then when are XPs actually useful?

When the stuff you want to do with them are not used to evaluate queries.
For instance, we have an extended stored procedure that performs a loopback
and writes messages to a log table when an error is detected. (The point
with the loopback is that we want the log records to persist even if there
is a rollback.)

Another possible application is some sort of signaling, to inform some
external process "Hey, I've just inserted 10000 rows, you might be
interested in those".

But it is correct that XP:s, as well as sp_OAcreate & co for calling
OLE objects, have limited use, and something you only use for special
cases.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]