Solved

SQL - Emailing result out

Posted on 2011-02-15
3
331 Views
Last Modified: 2012-05-11
I created 2 stored procedures.  One generate the email only and then calls another one to generate the records to send it out.  However, I can't seem to get it not to send an email if there's no records.  I don't want to keep sending emails out if there's no records.  How would I pass this back to the first stored procedure to exit?

From the first stored procedure, I tried adding the following before the "While @@FETCH_STATUS = 0 Begin", but without success.  It is still sending out the email.

IF @@FETCH_STATUS != 0
Begin
    Return
End

Any idea on what I need to do so that I don't send out an email if there's no record?
--FIRST SP TO SET EMAIL:
Declare @msg as varchar(8000)
Declare @ToSend As cursor
Declare @UserID as Varchar(15)
Declare @email as varchar(50)
Declare @Subject as Varchar(55)

Begin Set @Date = Convert(varchar(10), GetDate(), 101) End
Set @Subject = 'Sales Report for ' +  @Date

Set @ToSend = Cursor
    Select UserID, Email from Sales Where EmailSent = 1

Open @ToSend

Fetch Next From @ToSend Into @UserID, @EMail
While @@FETCH_STATUS = 0
Begin
       Exec SP_SalesInfo
       @UserId = @UserID,
       @msg = @msg output


       Exec msdb.dbo.sp_dbmail
        @profile_name = 'Sales',
        @Body_format='html',
        @Recipients = @EMail,
        @Subject = @Subject,
        @Body = @Msg

Fetch Next From @ToSend Into @UserID, @Email

End




--2ND SP TO GENERATE THE TABLE WITH RECORDS.
Declare @CO as Cursor
Declare @ItemNum As Varchar(15)
Declare @Qty as Decimal(10,2)


--Message Header
Set @msg = '<html>' + char(13) + char(10)
Set @msg = @msg + '<STYLE type="text/css"><!--' + char(13) + char(10)
set @msg = @msg + 'p {font-family: Tahoma; font-size: 12px;)' + char(13) + char(10)
set @msg = @msg + 'TD {font-family: Tahoma, font-size: 10px;)' + char(13) + char(10)
Set @msg = @msg + 'TD.hdr {font-family: Tahoma, font-size 10px; Color: #ffffff; background_color: #009912) + char(13) + char(10)
Set @msg = @msg + '--></STYLE> + char(13) + char(10)
Set @msg = @msg + '<body><p><FONT face="Tahoma" Size="2">Sales Report for '+@UserID+'</FONT></P>' + char(13) + char(10)

--Table Header
Set @msg = @msg + '<p>Sales report:' + char(13) + char(10)
set @msg = @msg + '<TABLE WIDTH="1000" Border="2" BorderColor="#0011ee" CellSpacing="0" Cellpadding="2">' + char(13) + char(10)
set @msg = @msg + '<TR><TD class="hdr" nowrap>Item Number</TD> + char(13) + Char(10)
set @msg = @msg + '<TD class="hdr" nowrap>Qty</TD></TR> + char(13) + char(10)

SET @CO = Cursor For

Select ItemNum, Qty From Cust_Order where UserID = @UserID

OPen @CO

FETCH NEXT FROM @CO INTO @ItemNum, @Qty
If @@FETCH_STATUS !=0
    Begin
       Set @msg=@msg + '<TR><TD>No records</TD></TR>' + char(13) + char(10)
    End

WHILE @@FETCH_STATUS = 0
Begin
    Set @msg=@msg + '<TR><TD nowrap>' + @ItemNum + '</TD> + char(13) + char(10)
    Set @msg=@msg + '<TD nowrap>' + @Qty + '</TD> + Char(13) + char(10)

    FETCH NEXT FROM @CO into @ItemNum, @Qty
End

Set @msg=@msg + '</Table></p> + Char(13) + char(10)

Close @CO

Return

Open in new window

0
Comment
Question by:holemania
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 6

Expert Comment

by:dan_mason
ID: 34899182
You have always got a value for @msg, so it always has content to send.

The easiest way to resolve this might be to put at the top of the 2nd sproc:

IF EXISTS (Select ItemNum, Qty From Cust_Order where UserID = @UserID)

Then nest the rest of the procedure inside a BEGIN...END
0
 
LVL 6

Accepted Solution

by:
dan_mason earned 500 total points
ID: 34899188
Sorry, more efficient to write:

IF EXISTS (SELECT 1 FROM Cust_Order WHERE UserID=@UserID)
0
 

Author Comment

by:holemania
ID: 34899501
Thank you.  That worked.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question