[SOLVED] SQL Server 2008 R2

SQL Server 2008 R2 - sp_send_dbmail

At the beginning I mark - I'm not a specialist in writing code. I need your help. I want to implement an email notification system via sp_send_dbmail.

Code:

USE msdb
go

SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON

EXEC sp_send_dbmail @profile_name='PROFILENAME',
    @recipients='myemail@email.com',
    @query_result_header=0,
    @attach_query_result_as_file=0,
    @query="select Kod,Nazwa from DATABASE.dbo.Towar where Kod NOT LIKE '%?%' and (AsId=205 or AsId=304 or AsId=289 or AsId=321 or AsId=306 or AsId=217 or AsId=261) and Aktywny=1 ORDER BY Kod",
    @body_format="text",
    @subject='SUBCJECT'
GO

The SQL query works and sends an e-mail, but also sends empty lines. for example. How to get rid of them?

1916                 SER TWAROGOWY WIEJSKI LUZ

I would also like sp_send_dbmail to send an email only when the query returns data. If there is no data - does not send messages.

Solution

I think you need IF, if you want a conditional email:

IF EXISTS (select Kod, Nazwa from DATABASE.dbo.Towar where Kod NOT LIKE '%?%' and AsId IN (205, 304, 289, 321, 306, 217, 261) and Aktywny = 1)
BEGIN
    EXEC sp_send_dbmail @profile_name='PROFILENAME',
        @recipients='myemail@email.com',
        @query_result_header=0,
        @attach_query_result_as_file=0,
        @query='select Kod, Nazwa from DATABASE.dbo.Towar where Kod NOT LIKE ''%?%'' and AsId IN (205, 304, 289, 321, 306, 217, 261) and Aktywny=1 ORDER BY Kod',
        @body_format='text',
        @subject='SUBCJECT';
END;

GO