Solved

SQL - Emailing result out

Posted on 2011-02-15
3
330 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

763 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