Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL - Emailing result out

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Integration Management Part 2
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

773 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