asked on

Stored Proceedure return @@ROWCOUNT problem

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

Raja Jegan R

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

dmzuser

ASKER

Nope not this.

WHERE (Email= 'none_exist_email@nowhere.com')

BrandonGalderisi

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.

dmzuser

ASKER

How do you do " Inside of stored procedures, SET commands effect @@Rowcount." ?

ASKER CERTIFIED SOLUTION

BrandonGalderisi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dmzuser

ASKER

thanks you

BrandonGalderisi

Was that the issue? Was there something before the @@rowcount check?

modus_operandi

Grade changed to 'A'

modus_operandi
EE Moderator