I'm not aware of any way of doing it through T-SQL, I would imagine you'd have to do something in VB or VBS in order to get a MsgBox to pop up for you. How it would be called from the Stored Proc, I'm not aware of (unless you exit out using xp_cmdshell and just have that call your exe or vbs file)
So something like:
Create proc msg_proc
exec master..xp_cmdshell 'c:\vbs\MessageBox.vbs'
(--any other code--)
Hope this helps, its crude, but in my lack of expertise I'm not aware of a more elegant way.