Store Procedure concatenating text

Question

I am looking for the best way to create a stored procedure which will return a line of text.
This text will simply be HTML code with ingested data. This will then be sent to Logic App to use in HTML body to create an email notification, which in a very simplified version may look like this:

Hello,
Here is your report:
<table with data>
Thank you

Some things like “Hello”, “Here is your report” are static, some dynamic like <table with data>
Here is my idea:

create procedure sp_emailbody  
@JsonData nvarchar (max)  
  
as   
begin  
  
declare @Body nvarchar (max)  
declare @EmailBody1 nvarchar (max)  
declare @EmailBody2 nvarchar (max)  
  
set @EmailBody1 = '< large html code>'   
set @EmailBody1 = '< large html code>'   
set @Body = @EmailBody1 + @JsonData + @EmailBody  
;  
execute (@Body)  
end  

I very afraid of concatenating a large amount of text. I have already prepared HTML which I quite large (200 line of code).

Hope to hear what would be the best approach here

Answer 1

this is simple example for you
you can add css too hier
but for large data you need to write best sql select (not difference output format)

declare @JsonData nvarchar(max)
declare @Body nvarchar (max)
 declare @EmailBody1 nvarchar (max)
 declare @EmailBody2 nvarchar (max)

 set @EmailBody1 = ' large html code' 
 set @EmailBody2 = ' large html code' 
 set @Body = @EmailBody1 + isnull(@JsonData,'') + @EmailBody2
 ;




DECLARE @tableHTML  NVARCHAR(MAX) ;    
SET @tableHTML =  
    N'<H1>Msdn Forum  Details</H1>' +  
    N'<table border="1">' +  
    N'<tr>
    <th>EmailBody1</th>
    <th>EmailBody2</th>
    <th>Body</th>'
    +


        N'</tr>' +  
    CAST ( ( SELECT td = @EmailBody1,       '',  
                    td = @EmailBody2, '',
                    td=@Body


              FOR XML PATH('tr'), TYPE   
    )

    AS NVARCHAR(MAX) ) +  
    N'</table>' ;

    select @tableHTML

Answer 2

Hi @braxx ，

Could you tell us your specific question, is the best method you want in terms of performance or statement simplification?If your data volume is not very large (million level), then the performance difference will not be obvious.

If you have any question, please feel free to let me know.

Regards
Echo

---

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 3

Hi @braxx ,

(X)HTML composition is relatively easy to implement in T-SQL by using T-SQL and XQuery.
It is much better approach in comparison with string concatenation.

Benefits:
• It creates a guaranteed proper (X)HTML.
• Easy to test in both SSMS and any internet browser.
• It is dynamic and can handle both SQL variables and tables.
• It supports both CSS styles: embedded and included.

Check it out here my answers on the forum on the same subject:

• html-code-inside-sql
• html-formatting-using-tsql

Answer 4

That is not a problem for the stored procedure. What is your concern?

However, your email system may limit the size of the email body. You would be better off sending an attachment.