Send email from a stored procedure

[Member 1284721]

I need to send an email from a stored procedure, I found I use msdb.sp_send_dbmail, can someone give a suggestion on how I can do it except calling this built-procedure in sql server? Thanks.

[GuyThiebaut]

I use sp_send_dbmail - I can't think of any other methods provided by sql server which will do this. For security reasons I have the stored procedure, which runs this, within the msdb database as certain permissions, which I am unwilling to give to users, are needed to run this stored procedure.

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

[Lost User]

The sproc is there to provide the functionality. If it is there, and works, and you need such, why ask for "any" solution that excludes it? Why "except"?

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]

[Corporal Agarn]

You can always have a .NET program run the procedure taking data from it and email from the .NET program.

[Member 1284721]

thank you very much for your reply. Can you tell me what the best practice is for notifying an developer if an error occurred in a stored procedure?

[GuyThiebaut]

Just throw the exception. There is no reason, I can think of, that you should need to catch an exception from a stored procedure. Throwing the exception will allow the developer to have an error message and pursue fixing the error.

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

[jschell]

Member 1284721 wrote:

Can you tell me what the best practice is for notifying an developer if an error occurred in a stored procedure?

What happens if the stored proc doesn't run at all?

[Lost User]

Please read here Try Catch in SQL - Reporting stored procedure error in email and also log the error in a table[^] You can see an error mechanism also in that link. Cheers :)