Using SQL Mail Part 2

Yesterday we talked about setting up SQL Mail.  Today we are going to talk about how we can take advantage of SQL Mail.  To do so, we need to be familiar with the sp_send_dbmail stored procedure.  This stored procedure allows for sending emails to a given list of recipients and many other options:

The format for the stored procedure looks something like this:

exec msdb.dbo.sp_send_dbmail @profile_name = 'profile_name'
    ,@recipients = 'recipients'
    ,@copy_recipients = 'copy_recipient'
    ,@blind_copy_recipients = 'blind_copy_recipient'
    ,@from_address = 'from_address'
    ,@reply_to = 'reply_to'
    ,@subject = 'subject'
    ,@body = 'body'
    ,@body_format = 'body_format'
    ,@importance = 'importance'
    ,@sensitivity = 'sensitivity'
    ,@file_attachments = 'attachment'
    ,@query = 'query'
    ,@execute_query_database = 'execute_query_database'
    ,@attach_query_result_as_file = attach_query_result_as_file
    ,@query_attachment_filename = query_attachment_filename
    ,@query_result_header = query_result_header
    ,@query_result_width = query_result_width
    ,@query_result_separator = 'query_result_separator'
    ,@exclude_query_output = exclude_query_output
    ,@append_query_error = append_query_error
    ,@query_no_truncate = query_no_truncate
    ,@query_result_no_padding = @query_result_no_padding
    ,@mailitem_id = mailitem_id OUTPUT

As you can see, there are many different options available when using this stored procedure.  Here are some items of note on the variables:

• @profile_name is the Profile Name used in the previous post.  The email will not go out if the profile_name is not spelled correctly.
• @recipients is a comma separated list of recipients.
• @body_format is the format to use for the body of the email.  The options are "Text" and "HTML".  The HTML options allows you to use HTML formatting for the email (which includes line breaks, etc.)
• @file_attachment is a comma separated list of files to attach to the email.
• @query (query to execute), @execute_query_database (database to execute the query on), @attach_query_result_as_file (selected whether to put the results as an attachment or in the body of the email, and @query_attachment_filename (the name for the attached query results) can all work together to give you different options when sending your email.

Once you have decided on your different options, you can then format your email message and include the SQL stored procedure when scheduling jobs or directly into stored procedures you have written.  You can then attach any results you want into the email.

As an example, let's say that I set up a stored procedure that emails customers once they have made a purchase.  I have variables that I would use in the stored procedure to perform this.  Those variables could include: @customernumber (customers account number), @ordernumber (order number), @customeremail (customers email address).  Using these variables, I could then send out an email on order like so:

declare @query1 varchar(4000) = 'select * from CustomerOrder where custombernumber = ' + @customernumber

exec msdb.dbo.sp_send_dbmail @profile_name = 'Test Profile'
    ,@recipients = @customeremail
    ,@from_address = 'myemail@gmail.com'
    ,@reply_to = 'myemail@gmail.com'
    ,@subject = 'Customer Order'
    ,@body = 'Attached is the information for your order'
    ,@body_format = 'HTML'
    ,@query = @query1
    ,@execute_query_database = 'MyDB'
    ,@attach_query_result_as_file = 1

To read more on SQL Mail, visit the following: SQL Mail

SQL Stored Procedures

Yesterday I talked about SQL Functions and their benefits.  Today I wanted to talk about SQL Stored Procedure.  Much like a SQL Function, a Stored Procedure takes in parameters and performs certain actions.  A Stored Procedure can do more, though, which would include the following:

• Update data within the database
• Return more than one value
• Call (run) other stored procedures
• Return status flag on whether or not the process completed successfully or failed

So why would someone have need for a SQL Stored Procedure?  Let's take the situation, for example, that you are an online company that takes sales orders, much like Amazon.com or eBay.  Every time someone completes an order, you have two choices.

1. Have the web interface (website) run certain insert statements into your database so the order is tracked.
2. Have the web interface (website) run one or multiple Stored Procedures to insert the order into your database

Both options are perfectly viable, but for option #1, anyone who understands HTML code can easily capture your statements and use it to hack into your database.

The second option, on the other hand, not only obfuscates (hides) data about your database, but you can also more easily control what happens if an error occurs at any moment in the insert.  This could include trying to run the stored procedure again if it fails, or show a pop-up window to a customer on failure.  These stored procedures can also be set up to fulfill your company's business rules.  And then, if for whatever reason your business rules change, you just have to update your stored procedure, instead of your website.

The syntax to create a stored procedure looks like the following:


CREATE PROCEDURE  
-- Add the parameters for the stored procedure here
<@Param1, sysname, @p1> = , 
<@Param2, sysname, @p2> =
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

    -- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END

If I wanted to create a stored procedure that returned my name, it would look like this:

CREATE PROCEDURE myName

AS

BEGIN

SELECT 'Bill Lantz'

END

Of course, stored procedure can be much more complicated than this.  For instance, if you wanted to allow someone to provide a customer first name, last name, and phone number and then insert it into your customer table, it would look something like this:


CREATE PROCEDURE insertCustomer
(
@LastName varchar(100),
@FistName varchar(100),
@PhoneNumber varchar(10)
)
AS
BEGIN

Insert into Customer
Values (@LastName, @FirstName, @PhoneNumber)

END

How do you think SQL Stored Procedures would work at your organization?