Requesting help creating send dbmail stored proc and then calling it from another proc

Question

Post reply

Requesting help creating send dbmail stored proc and then calling it from another proc

mklein-768586

SSC Journeyman

Points: 97
More actions
February 26, 2010 at 8:28 am

#230318

Goal: Send an email when a specific stored procedure is run
1. Create dbmail send stored procedure
2. Call or execute the mail stored procedure from the other stored procedure
I can't even create the stored procedure. It's telling me the EXEC line is incorrect. Here is the code:
USE [CVEpi]
GO
/****** Object: StoredProcedure [dbo].[EmailEventSend] Script Date: 02/25/2010 13:23:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Version 0.1 common email procedure
CREATE PROCEDURE [dbo].[EmailEventSend]
--Send Email Notification
EXEC msdb.dbo.sp_send_dbmail
@profile_name='Basic',
@recipients='mike@somewhere.com',
@subject='You have a new event',
@body='A new engagement has been requested. Please check the website'
--EndEmail
Then, how do I call or execute this stored procedure from within another stored procedure.
Thanks so much for your help.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323743 More actions · Answer 1

at a minimum, you are missing the AS keyword:

CREATE MyProc

AS--buncha code

CREATE PROCEDURE [dbo].[EmailEventSend]

--Send Email Notification

AS

BEGIN

EXEC msdb.dbo.sp_send_dbmail

@profile_name='Basic',

@recipients='mike@somewhere.com',

@subject='You have a new event',

@body='A new engagement has been requested. Please check the website'

--EndEmail

--Do other stuff

END

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mklein-768586 SSC Journeyman Points: 97 More actions · Answer 2

Lowell,

Thank you very much. It's always the little things that make the biggest problems. Your solution, of course, worked.

Mike

Lowell SSC Guru Points: 323743 More actions · Answer 3

yeah, pre-caffeine coding messes me up almost every day; I know how the little things can getcha.

glad you got it now!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mklein-768586 SSC Journeyman Points: 97 More actions · Answer 4

Can you tell me how to call the stored proc EmailEventSend from within another stored proc? I need to send an email when that stored proc is run.

Thanks.

Lowell SSC Guru Points: 323743 More actions · Answer 5

easy...you just have to explicitly use the EXEC or EXECUTE command:

CREATE PROCEDURE MyProc (@tblname sysname)

AS

BEGIN

SET NOCOUNT ON

--ack! someone used this proc! send an email

--current incarnation of EmailEventSend has no parameters

EXEC EmailEventSend

--do other stuff

SELECT * FROM sys.objects WHERE name = @tblname

END

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!