Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL - Emailing result out

Posted on 2011-02-15
3
Medium Priority
?
333 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 2000 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

TCP/IP Network Protocol Cheat Sheet

TCP/IP is a set of network protocols which is best known for connecting the machines that make up the Internet. The truth is that TCP/IP is one of the oldest network protocols and its survival is mainly based on its simplicity and universality.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

660 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