Hi,
To answer your questions:
1. Yes, a custom pipeline component is a good choice to do logging. Another alternative would be to use WCF SQL Adapter (to call your stored procedure), or if you can do away with your SP, then use MS Enterprise Library’s Logging Application.
2. I would place this component in the Pre-Assemble stage of your Send pipeline.
Also, you might want to check out the following blog about a custom Streaming message logger pipeline component (with downloadable solution):
http://monishnagisetty.wordpress.com/2008/04/06/dbmessagelogger-a-streaming-message-logger-pipeline-component/
Hope this helps,
Daniel.