Generating Email List using SPROC

Every now and then we need to send emails to users. Sometimes we need to send the same email to multiple users and hence need some mechanism through which we can retrieve email addresses from the source, commonly database. In this article we will see how you can retrieve email addresses from the database using a simple Stored Procedure.

Introduction:

Every now and then we need to send emails to users. Sometimes we need to send the same email to multiple users and hence need some mechanism through which we can retrieve email addresses from the source, commonly database. In this article we will see how you can retrieve email addresses from the database using a simple Stored Procedure.

What will Stored Procedure Return:

Stored procedure can return the whole list of emails and we can format it at the client side. But in this case the Stored Procedure will be returning already formatted string which has email addresses separated by commas.

Stored Procedure:

Let's take a look at the complete stored procedure and than we will dissect it.


CREATE PROCEDURE [usp_GetEmailList]
@EmailList nvarchar(200) OUTPUT
AS
DECLARE @Email nvarchar(50)
--DECLARE @EmailList nvarchar(200)
SET @EmailList = ''
DECLARE Email_Cursor CURSOR FOR

SELECT Email FROM tblPerson
OPEN Email_Cursor
FETCH NEXT FROM Email_Cursor
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Email_Cursor
INTO @Email

SET @EmailList = @EmailList + @Email + ','

END

SET @EmailList = SUBSTRING(@EmailList,0,LEN(RTRIM(@EmailList)))

CLOSE Email_Cursor
DEALLOCATE Email_Cursor

GO
 

The first thing we did is to make an OUTPUT variable so we can send it back to the calling code. Our variable @EmailList will contain the emails of users separated by comma. Next important thing we did is to make the use of Cursors since we need to traverse through each row individually. It will fetch the data from the tblPerson until there is no row left. Each email address is put into the @Email variable and later concatenated into @EmailList variable with the addition of a comma.

When the cursor finishes its job than there will be a comma at the end of the @EmailList. We remove that by using the SUBSTRING function of the SQL SERVER.

Finally we close the cursor and DEALLOCATE the memory assigned to the cursor.

Displaying Email List on the Asp.net Page:

Since most of the work is already done by the stored procedure we just need to retrieve the @EmailList variable.

SqlCommand cmd = new SqlCommand("usp_GetEmailList",myConnection);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@EmailList",SqlDbType.NVarChar,200);

cmd.Parameters["@EmailList"].Direction = ParameterDirection.Output;

myConnection.Open();

cmd.ExecuteNonQuery();

string emailList = (string) cmd.Parameters["@EmailList"].Value;

myConnection.Close();

Response.Write(emailList);

We used @EmailList as the OUTPUT parameter in the Stored Procedure and that's why we are setting the direction of the @EmailList variable to ParameterDirection.Output.

Finally, we retrieve the email list into the emailList variable of type string.

string emailList = (string) cmd.Parameters["@EmailList"].Value;

All that is left is to send the email. Please refer to my article Sending Emails in Asp.net.

Here is another and a better way to get the comma delimited list:

DECLARE @EmailList nvarchar(100)
SELECT @EmailList = COALESCE(@EmailList,'') + COALESCE(Email,'') + ',' FROM tblPerson
WHERE Email IS NOT NULL
SET @EmailList = SUBSTRING(@EmailList,0,LEN(RTRIM(@EmailList)))
PRINT @EmailList

I hope you liked the article, happy coding!