CREATE PROCEDURE [dbo].[SendEmailUsingSMTP]

@Profile varchar(300), — You have to create a profile for email sending. You can get  details from here: http://msdn.microsoft.com/en-us/library/ms187605.aspx Or http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/
@From varchar(MAX), — Is a semicolon-delimited list
@To varchar(MAX), — Is a semicolon-delimited list
@Subject nvarchar(255),
@Body varchar(MAX),
@AttachedFilePath nvarchar(max), — Is a semicolon-delimited list of file names to attach to the e-mail message. Files in the list must be specified as absolute paths.
@Query nvarchar(max),
@QueryExecuteDatabase sysname,
@QueryResultAsFile bit, — 0 Means with body after content, 1 means with attachment
@QueryResultFileName nvarchar(255) — If NULL then system product a default name

AS

EXEC sp_configure ‘show advanced’, 1

RECONFIGURE

EXEC sp_configure ‘Database Mail XPs’, 1

RECONFIGURE

IF @AttachedFilePath IS NULL SET @AttachedFilePath = ”
IF @Query IS NULL SET @Query = ”

DECLARE @SqlVersion varchar(255)
SELECT @SqlVersion = @@VERSION
SET @SqlVersion = Upper(Replace(@SqlVersion, ‘ ‘, ”))
–select @SqlVersion, CHARINDEX(‘SERVER2005’, @SqlVersion)

— If SQL version is 2000
If CHARINDEX(‘SERVER2000’, @SqlVersion) > 0
BEGIN

— We will not process email sending for sql version 2000
RETURN

END

— If SQL version is 2005
If CHARINDEX(‘SERVER2005’, @SqlVersion) > 0
BEGIN

— Has both attachment and query need to execute and send to file as attachment
If LEN(@AttachedFilePath) > 0 AND LEN(@Query) > 0

BEGIN

EXEC msdb.dbo.sp_send_dbmail
@profile_name = @Profile,
@recipients = @To,
@copy_recipients = @From,
@subject = @Subject,
@body = @Body,
@file_attachments = @AttachedFilePath,
@query = @Query,
@execute_query_database = @QueryExecuteDatabase,
@attach_query_result_as_file = @QueryResultAsFile,
@query_attachment_filename = @QueryResultFileName

END

— Has only query need to execute and send to file as attachment
If LEN(@AttachedFilePath) = 0 AND LEN(@Query) > 0
BEGIN

EXEC msdb.dbo.sp_send_dbmail
@profile_name = @Profile,
@recipients = @To,
@copy_recipients = @From,
@subject = @Subject,
@body = @Body,
@query = @Query,
@execute_query_database = @QueryExecuteDatabase,
@attach_query_result_as_file = @QueryResultAsFile,
@query_attachment_filename = @QueryResultFileName

END

— Has only query need to execute and send to file as attachment
If LEN(@AttachedFilePath) = 0 AND LEN(@Query) = 0
BEGIN

EXEC msdb.dbo.sp_send_dbmail
@profile_name = @Profile,
@recipients = @To,
@copy_recipients = @From,
@subject = @Subject,
@body = @Body

END

END
— If SQL version is 2008 or later
ELSE
BEGIN

— Has both attachment and query need to execute and send to file as attachment
If LEN(@AttachedFilePath) > 0 AND LEN(@Query) > 0
BEGIN

EXEC msdb.dbo.sp_send_dbmail
@profile_name = @Profile,
@recipients = @To,
@from_address = @From,
@subject = @Subject,
@body = @Body,
@file_attachments = @AttachedFilePath,
@query = @Query,
@execute_query_database = @QueryExecuteDatabase,
@attach_query_result_as_file = @QueryResultAsFile,
@query_attachment_filename = @QueryResultFileName

END

— Has only query need to execute and send to file as attachment
If LEN(@AttachedFilePath) = 0 AND LEN(@Query) > 0
BEGIN

EXEC msdb.dbo.sp_send_dbmail
@profile_name = @Profile,
@recipients = @To,
@from_address = @From,
@subject = @Subject,
@body = @Body,
@query = @Query,
@execute_query_database = @QueryExecuteDatabase,
@attach_query_result_as_file = @QueryResultAsFile,
@query_attachment_filename = @QueryResultFileName

END

— Has only query need to execute and send to file as attachment
If LEN(@AttachedFilePath) = 0 AND LEN(@Query) = 0
BEGIN

EXEC msdb.dbo.sp_send_dbmail
@profile_name = @Profile,
@recipients = @To,
@from_address = @From,
@subject = @Subject,
@body = @Body

END

END

 

GO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s