Solved

SQL - Emailing result out

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

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. …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

776 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