Rows Affected by a select query for sp_send_dbmail

Question

Post reply

Rows Affected by a select query for sp_send_dbmail

mustapha-721538

Right there with Babe

Points: 774
More actions
July 1, 2010 at 1:55 am

#97059

Hi,
How can I get the rows affected by a select query has any rows or not in order to send a mail.
If the query returns at least one row then the mail will be sent.
else do nothing.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

You could test for existing rows prior to invoking sp_send_dbmail.

Something similar to this:

DECLARE @sql nvarchar(4000)

DECLARE @sqltest nvarchar(4000)

DECLARE @testResults TABLE (rowsAffected int)

SET @sql = 'SELECT someColumn FROM someTable'

SET @sqltest = 'SELECT COUNT(*) FROM (' + @sql + ') AS Test'

INSERT @testResults EXEC(@sqlTest)

IF (SELECT rowsAffected FROM @testResults) > 0

BEGIN

EXEC sp_send_dbmail @profile_name = 'profile_name' ,

@recipients = 'recipients' ,

@subject = 'subject',

@query = @sql

END

Hope this helps

Gianluca

-- Gianluca Sartori

mustapha-721538 Right there with Babe Points: 774 More actions · Answer 2

Parsing the code is green but when executing I´m geting the following error I know it has to do

with the single and double qoutes can you please help to correct the following code:

DECLARE @sql nvarchar(4000)

DECLARE @sqltest nvarchar(4000)

DECLARE @testResults TABLE (rowsAffected int)

SET @sql = 'SELECT CAST(co.InvoiceNo as varchar(10)),

CAST(co.CustomerNo AS VARCHAR(10))' + '' - '' + ' co.CustomerName,

col.ArticleNo,

col.Invoiced,

col.Name,

col.QuantityPerUnitText,

left(convert(varchar, col.created, 120),10),

CAST(emp.UserNo AS VARCHAR(10))' + '' - '' + ' emp.Name

FROM AydinGLOBALData.Aydin.CustomerOrderLineCopy as col, AydinGLOBALData.Aydin.CustomerOrderCopy as co, AydinGLOBALData.Aydin.Employee as emp

WHERE col.QuantityPerUnitText=''KG'' and col.InvoiceNo = co.InvoiceNo AND col.CreatedBy = emp.UserNo

AND left(convert(varchar, co.invoicedate, 120),10) = left(convert(varchar, getdate(), 120),10) and co.InvoiceNo>0

AND CAST(col.Invoiced AS decimal(10,2)) - CAST(col.Invoiced AS int)=0

ORDER BY CAST(co.CustomerNo AS VARCHAR(10))' + '' - '' + ' co.CustomerName'

SET @sqltest = 'SELECT COUNT(*) FROM (' + @sql + ') AS Test'

INSERT @testResults EXEC(@sqlTest)

IF (SELECT rowsAffected FROM @testResults) > 0

BEGIN

EXEC sp_send_dbmail @profile_name = 'Visma' ,

@recipients = 'thanks@forthehelp.se' ,

@subject = 'subject',

@query = @sql

END

GO

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

spaghettidba

SSC Guru

Points: 105732

July 1, 2010 at 6:50 am

#1187633

What error are you getting?

-- Gianluca Sartori

mustapha-721538 Right there with Babe Points: 774 More actions · Answer 4

Msg 8117, Level 16, State 1, Line 5

Operand data type varchar is invalid for subtract operator.

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

Replace your code with this:

SET @sql = '

SELECT CAST(co.InvoiceNo as varchar(10)),

CAST(co.CustomerNo AS VARCHAR(10)) + '' - '' + co.CustomerName,

col.ArticleNo,

col.Invoiced,

col.Name,

col.QuantityPerUnitText,

left(convert(varchar, col.created, 120), 10),

CAST(emp.UserNo AS VARCHAR(10)) + '' - '' + emp.Name

FROM AydinGLOBALData.Aydin.CustomerOrderLineCopy as col,

AydinGLOBALData.Aydin.CustomerOrderCopy as co,

AydinGLOBALData.Aydin.Employee as emp

WHERE col.QuantityPerUnitText = ''KG''

and col.InvoiceNo = co.InvoiceNo

AND col.CreatedBy = emp.UserNo

AND left(convert(varchar, co.invoicedate, 120), 10) = left(convert(varchar, getdate(), 120), 10)

and co.InvoiceNo > 0

AND CAST(col.Invoiced AS decimal(10, 2)) - CAST(col.Invoiced AS int) = 0

ORDER BY CAST(co.CustomerNo AS VARCHAR(10)) + '' - '' + co.CustomerName

'

It should work fine.

-- Gianluca Sartori

mustapha-721538 Right there with Babe Points: 774 More actions · Answer 6

Thanks it works perfect after adding an alias to the columns that was computed or concatenated.

I need to understand the scope of the variables and to know if @testResults is a variable or a table?

Please can you comment the following rows:

DECLARE @sql nvarchar(4000)

DECLARE @sqltest nvarchar(4000)

DECLARE @testResults TABLE (rowsAffected int)

SET @sqltest = 'SELECT COUNT(*) FROM (' + @sql + ') AS Test'

INSERT @testResults EXEC(@sqlTest)

IF (SELECT rowsAffected FROM @testResults) > 0

spaghettidba SSC Guru Points: 105732 More actions · Answer 7

Glad I could help.

Let's see if this comments help you understanding what goes on behind the scenes:

-- Variable: sql string to execute

DECLARE @sql nvarchar(4000)

-- Variable: sql string to test for existing rows

DECLARE @sqltest nvarchar(4000)

-- Table Variable to hold number of rows affected by sql query

-- This is a table variable, something similar to a temporary table. Look it up on BOL.

-- I suggest that you read this great article[/url] on the subject.

DECLARE @testResults TABLE (rowsAffected int)

-- Build a sql to test the count from your sql statement

SET @sqltest = 'SELECT COUNT(*) FROM (' + @sql + ') AS Test'

-- Insert the count into table variable for later reuse

INSERT @testResults EXEC(@sqlTest)

-- Send mail if rows returned from original sql statement are > 0

IF (SELECT rowsAffected FROM @testResults) > 0

-- Gianluca Sartori