Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Stored Proceedure return @@ROWCOUNT problem

Posted on 2009-02-18
8
Medium Priority
?
450 Views
Last Modified: 2013-11-05
I am trying to sent an email if record exist inside the stored procedure but my @@ROWCOUNT doesn't work.
My problem is, this script always sending email out when it doesn't have any record on it.
Please let me know what is wrong with my script.

thanks


CREATE   PROCEDURE sp_send_email
AS 
    BEGIN 
        DECLARE @Body VARCHAR(8000) 
        DECLARE @Subject_str VARCHAR(8000) 
        DECLARE @Group_id VARCHAR(8000) 
        DECLARE @SingleEmail VARCHAR(8000) 
 
		--Quering the records
        SELECT @SingleEmail =Email,@Group_id=Group_id
        FROM group_product_setup
        WHERE (Email= 'none_exist_email@nowhere.com)
 
          --If rowcount is  greater than 0 then send email
        IF @@ROWCOUNT>0 
              
                SET @Body = '<html><body> '+'
                <p>TEST MESSAGE<br>
                </body></html>'
 
                --send email  SE-100 begin
                SET @Subject_str='Approval Require for GroupID '+@Group_id+''
                EXEC master..xp_smtp_sendmail
                @TO = 'XXXXXX@XXXXXX.com', 
                @BCC = @SingleEmail, 
                @from = 'XXXXXX@XXXXXX.com', 
                @priority = 'HIGH',
                @type = 'text/html',
                @subject = @Subject_str, 
                @message = @Body, 
                @server = 'XXXXXX.server' 
                --send email  SE-100  ended
                
        
        END
GO

Open in new window

0
Comment
Question by:dmzuser
8 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 23671329
A small syntactical mistake ( ')..
Fixed it.. Give it a try
CREATE   PROCEDURE sp_send_email
AS 
    BEGIN 
        DECLARE @Body VARCHAR(8000) 
        DECLARE @Subject_str VARCHAR(8000) 
        DECLARE @Group_id VARCHAR(8000) 
        DECLARE @SingleEmail VARCHAR(8000) 
 
                --Quering the records
        SELECT @SingleEmail =Email,@Group_id=Group_id
        FROM group_product_setup
        WHERE (Email= 'none_exist_email@nowhere.com')
 
          --If rowcount is  greater than 0 then send email
        IF @@ROWCOUNT>0 
              
                SET @Body = '<html><body> '+'
                <p>TEST MESSAGE<br>
                </body></html>'
 
                --send email  SE-100 begin
                SET @Subject_str='Approval Require for GroupID '+@Group_id+''
                EXEC master..xp_smtp_sendmail
                @TO = 'XXXXXX@XXXXXX.com', 
                @BCC = @SingleEmail, 
                @from = 'XXXXXX@XXXXXX.com', 
                @priority = 'HIGH',
                @type = 'text/html',
                @subject = @Subject_str, 
                @message = @Body, 
                @server = 'XXXXXX.server' 
                --send email  SE-100  ended
                
        
        END
GO

Open in new window

0
 

Author Comment

by:dmzuser
ID: 23671489
Nope not this.

WHERE (Email= 'none_exist_email@nowhere.com')
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23671887
Is there something else you removed for privacy reasons?

Because this is valid:
--
                --Quering the records
        SELECT @SingleEmail =Email,@Group_id=Group_id
        FROM group_product_setup
        WHERE (Email= 'none_exist_email@nowhere.com)
 
          --If rowcount is  greater than 0 then send email
        IF @@ROWCOUNT>0
--

But this, would cause the @@Rowcount to be 1

--
                --Quering the records
        SELECT @SingleEmail =Email,@Group_id=Group_id
        FROM group_product_setup
        WHERE (Email= 'none_exist_email@nowhere.com)

        set @BCC = @SingleEmail

          --If rowcount is  greater than 0 then send email
        IF @@ROWCOUNT>0
--



Inside of stored procedures, SET commands effect @@Rowcount.

I think you probably left something out.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:dmzuser
ID: 23674450
How do you do " Inside of stored procedures, SET commands effect @@Rowcount." ?

0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 1000 total points
ID: 23674510
@@rowcount is the number of rows effected by the last statement.  If you are doing any type of error checking, it will also reset @@rowcount.


ex:

select 1
 
if @@Rowcount>0
print '@@Rowcount>0'
go
 
select 1
if @@error <> 0 goto exit_error
 
if @@Rowcount>0
print '@@Rowcount>0'
else
print '! @@rowcount>0'
 
exit_error:

Open in new window

0
 

Author Comment

by:dmzuser
ID: 23675937
thanks you
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23677685
Was that the issue?  Was there something before the @@rowcount check?
0
 
LVL 1

Expert Comment

by:modus_operandi
ID: 24286952
Grade changed to 'A'
 
modus_operandi
EE Moderator
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

578 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