SQL Query with string and Tab as an email format.

Question

Hello Everyone: I have a table with column names below :

Select * from Table

Column1 Column2 Column3
California area Report California Beach Program John Doe

Here is my query: But it's giving me an error :
Select concat('Good Morning',' ',Column3, ':',

'Attached please find '+datename(month, dateadd(month,-1,getdate()))+' +'Year(Getdate()+' report for review. Please direct reporting questions to Dev@Stuff .com.'

,'Report Name:','+Column1+'
,'Program name:','+Column2+',

'Kindest regards,
California Team
Director of Team') as FullMessage from Table

Below is the format of how I need this column : RESULT

Good Morning John Doe:
Attached please find the August 2020 report for review. Please direct reporting questions to Dev@Stuff .com.'

Report Name: California Area Report
Program name: California Beach Program

Kindest regards,
California Team
Director of Team

Please help!

Answer 1

Hi @kkran ,

Actually your output will be truncated if you use below query:

Select FullMessage = Concat('Good Morning ' , Column3 , ':' + CHAR (13) + CHAR (10) ,  
'Attached please find the '+ datename(month, dateadd(month,-1,getdate())) + ' ' + convert(varchar(4), Year(Getdate())) + ' report for review. Please direct reporting questions to Dev@gmail.com.' + CHAR (13) + CHAR (10) ,  
'Report Name: ' + Column1 + CHAR (13) + CHAR (10) ,  
'Program name: ' + Column2 + CHAR (13) + CHAR (10) ,  
'Kindest regards, ' + CHAR (13) + CHAR (10) ,  
'California Team' + CHAR (13) + CHAR (10) ,  
'Director of Team')  
from [table]  

Output:
Good Morning John Doe:
Attached please find the August 2020 report for review. Please direct reporting questions to Dev@Stuff .com.
Report Name: California area Report
Program name: California Beach Program
Kindest regards,
California Team
Director o

Please refer the updated query from below:

declare @FullMessage nvarchar(max)  
  
select @FullMessage = N'Good Morning ' + Column3 + ':' + CHAR (13) + CHAR (10) +  
'Attached please find the '+ datename(month, dateadd(month,-1,getdate())) + ' ' + convert(varchar(4), Year(Getdate())) + ' report for review. Please direct reporting questions to Dev@gmail.com.' + CHAR (13) + CHAR (10) +  
CHAR (13) + CHAR (10) +  
'Report Name: ' + Column1 + CHAR (13) + CHAR (10) +  
'Program name: ' + Column2 + CHAR (13) + CHAR (10) +  
CHAR (13) + CHAR (10) +  
'Kindest regards, ' + CHAR (13) + CHAR (10) +  
'California Team' + CHAR (13) + CHAR (10) +  
'Director of Team'   
from [table]  
  
print @FullMessage  

Output:
Good Morning John Doe:
Attached please find the August 2020 report for review. Please direct reporting questions to Dev@Stuff .com.

Report Name: California area Report
Program name: California Beach Program

Kindest regards,
California Team
Director of Team

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

While it is being slower in execution, you should do yourself a favour and use a template and placeholder replacement. E.g.

DECLARE @Body NVARCHAR(MAX) = N'Good Morning {ADDRESSEE_NAME}:
Attached please find the {REPORT_PERIODE} report for review. 
Please direct reporting questions to Dev@gmail.com.

Report Name: {REPORT_NAME}
Program name: {PROGRAM_NAME}

Kindest regards,
California Team
Director of Team';

DECLARE @AddresseeName NVARCHAR(MAX);
DECLARE @ProgramName NVARCHAR(MAX);
DECLARE @ReportDate DATE = DATEADD(MONTH, -1, GETDATE());
DECLARE @ReportPeriode NVARCHAR(MAX) = DATENAME(MONTH, @ReportDate) + N' ' + CONVERT(VARCHAR(4), YEAR(@ReportDate));
DECLARE @ReportName NVARCHAR(MAX);

SELECT @AddresseeName = Column3 ,
       @ReportName = Column1 ,
       @ProgramName = Column2
FROM   [table];

SET @Body = REPLACE(@Body, '{ADDRESSEE_NAME}', ISNULL(@AddresseeName, 'n/a'));
SET @Body = REPLACE(@Body, '{PROGRAM_NAME}', ISNULL(@ProgramName, 'n/a'));
SET @Body = REPLACE(@Body, '{REPORT_PERIODE}', ISNULL(@ReportPeriode, 'n/a'));
SET @Body = REPLACE(@Body, '{REPORT_NAME}', ISNULL(@ReportName, 'n/a'));
PRINT @Body;

I changed the date calculus, as I'm pretty sure you want "December 2020" in January 2021, not "December 2021".
The usage of ISNULL() is required, if the columns in the table can return NULL.

Answer 3

The problem is that you are mixing syntax for concatenation. You use the concat function, but in the middle of it, you also use the plus operator to concatenate strings. And then you lose yourself in all this.

Use concat, and use only comma between the arguments. Furthermore, in your code, put each argument to concat on a separate line terminated by comma. This way, if you get a syntax error, you can easily find where it is and amend it.

To get newlines in your output, use char(13) followed by char(10) to get a carriage return + line feed.

Answer 4

Hi @kkran ,

It seems you missed my earlier comment.
"...I would suggest to use email in HTML format.
This way you can easily format it, use CSS for fonts, colors, tables, etc..."

Check it out here: html-code-inside-sql

Answer 5

How do I create a tab/space between the lines?

Select FullMessage = 'Good Morning ' + Column3 + ':' + CHAR (13) + CHAR (10) +
'Attached please find the '+ datename(month, dateadd(month,-1,getdate())) + ' ' + convert(varchar(4), Year(Getdate())) + ' report for review. Please direct reporting questions to Dev@Stuff .com.' + CHAR (13) + CHAR (10) +
'Report Name: ' + Column1 + CHAR (13) + CHAR (10) +
'Program name: ' + Column2 + CHAR (13) + CHAR (10) +
'Kindest regards, ' + CHAR (13) + CHAR (10) +
'California Team' + CHAR (13) + CHAR (10) +
'Director of Team'
from Table