p tag in ms sql html with @variable

Question

I have a SQL that write birthday emails in html format... it work fine, but if I want to fill fields with variables, it does not work.

for example ma p tag:

<p style="margin: 0; color: #f07d00; font-family: Open Sans, arial, sans-serif; font-size: 12px; font-weight: bold; text-transform: uppercase;">
@DISPONAME
</p>

So, in the email it displays @DISPONAME, that a variable which is set in top of the sql, befor the html is start. Now I want to active the @DISPONAME like '+ @DISPONAME +' and it does not work, the email will be created till this and the rest is blank and the @DISPONAME row is not shown too...

What is wrong? How do I insert it correct?

Thank you!

Erik

Edit: SQL Command before html starts...

While (select top 1 id from dbo.Geburtstag_aktiv where send_date is NULL) is not NULL Begin DECLARE @ID int, @DISPONENT nvarchar(100), @DISPONAME nvarchar(100), @EMAILMA nvarchar(100), @EMAILDISPO nvarchar(50), @copy nvarchar(50) DECLARE @MOBIL as nvarchar(100), @DURCHWAHL as nvarchar(100), @BRIEFANREDE as nvarchar(100), @MABRIEFANREDE as nvarchar(100) DECLARE @sub nvarchar(255), @tableHTML nvarchar(MAX), @HomeServerUrl nvarchar(100), @BEDIENERTEXT as nvarchar(100), @BEDIENERFAX as nvarchar(100), @BEDIENERBERUF as nvarchar(100) SET @ID = (select top 1 id from dbo.Geburtstag_aktiv where send_date is NULL) SET @DISPONENT = (select LTRIM(RTRIM(DISPONENT)) from dbo.Geburtstag_aktiv where ID = @ID) SET @DISPONAME = (select LTRIM(RTRIM(DISPONAME)) from dbo.Geburtstag_aktiv where ID = @ID) SET @EMAILMA = (select LTRIM(RTRIM(EMAILMA)) from dbo.Geburtstag_aktiv where ID = @ID) SET @EMAILDISPO = (select LTRIM(RTRIM(EMAILDISPO)) from dbo.Geburtstag_aktiv where ID = @ID) SET @BRIEFANREDE = (select LTRIM(RTRIM(BRIEFANREDE)) from dbo.Geburtstag_aktiv where ID = @ID) SET @copy = copy SET @MOBIL = (select LTRIM(RTRIM(MOBIL)) from dbo.Geburtstag_aktiv where ID = @ID) SET @DURCHWAHL = (select LTRIM(RTRIM(DURCHWAHL)) from dbo.Geburtstag_aktiv where ID = @ID) SET @sub = 'Herzlichen Glückwunsch zum Geburtstag!' SET @HomeServerUrl = (select single_value from [dbo].[tb_admin_single_value] where single_value_desc = 'HomeServerUrl') SET @BEDIENERTEXT = (select LTRIM(RTRIM(BEDIENERTEXT2)) from dbo.Geburtstag_aktiv where ID = @ID) SET @BEDIENERFAX = (select LTRIM(RTRIM(FAX)) from dbo.Geburtstag_aktiv where ID = @ID) SET @BEDIENERBERUF = (select LTRIM(RTRIM(DISPOBERUF)) from dbo.Geburtstag_aktiv where ID = @ID) SET @MABRIEFANREDE = (select LTRIM(RTRIM(BRIEFANREDE)) from dbo.Geburtstag_aktiv where ID = @ID)

edit: Code to send the email:

SET @tableHTML = 
'<html>'
Html code
</html>';

EXEC msdb.dbo.sp_send_dbmail 

@recipients = @EMAILMA, 

@profile_name = 'SERVER2',

@subject = @sub,

@body = @tableHTML,

@body_format = 'HTML'

edit: here are some html code, which does not work with @variable

<tr>
<td>
<a href="callto:035100000@DURCHWAHL" style="margin: 0; color: #000; font-family: Open Sans, arial, sans-serif; font-size: 10px; text-decoration: none;">
Tel.: 0351 / 00000-@DURCHWAHL
</a>
</td>
</tr>

Can you add the SQL statement? It's hard to fix code that isn't included in the question. — David Rushton, Apr 26 '17 at 13:20
Your code does not show the tags... try to indent by at least 4 spaces... — Shnugo, Apr 26 '17 at 14:40
Your edit shows some strange *Html code*. Where is this coming from, what is it? — Shnugo, Apr 26 '17 at 14:42

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

2

First of all: You should never create (X)HTML or XML on string level!!

Just imagine something like

SELECT '<p>' + 'This is bad & dangerous -->  don''t do it!' + '</p>';

Due to the & and the > this will lead to invalid XML/HTML!

Try it like this

SELECT 'margin: 0; color: #f07d00; font-family: Open Sans, arial, sans-serif; font-size: 12px; font-weight: bold; text-transform: uppercase;' AS [@style]
      ,'Some Content'
FOR XML PATH('p');

The second point is:

email will be created till this and the rest is blank

In case the variable is NULL for any reason, the concatenation will fail.

SELECT 'Something' + NULL; --returns NULL...

The approach I showed you above is more tolerant here. Just try it with a NULL and you will still get a valid answer:

SELECT 'margin: 0; color: #f07d00; font-family: Open Sans, arial, sans-serif; font-size: 12px; font-weight: bold; text-transform: uppercase;' AS [@style]
      ,NULL
FOR XML PATH('p');

Btw: Schönen Gruß nach Dresden, ich war da gerade :-D

UPDATE Just a hint:

Your query calls the same table valued function over and over. This should be much easier with something like this

SELECT @DISPONENT = LTRIM(RTRIM(DISPONENT)) 
      ,@DISPONAME = LTRIM(RTRIM(DISPONAME))
      ,@EMAILMA   = LTRIM(RTRIM(EMAILMA))
      --more variables
FROM dbo.Geburtstag_aktiv where ID = @ID;

edited Jun 20 '20 at 09:12

Community

1
1

answered Apr 26 '17 at 14:06

Shnugo

66,100
9
53
114

danke für den Gruß ;-) thx for answering...there are so many tables and lines and so on in the email. how can I define it? and I do not understand where to insert the code you posted... SELECT 'margin: 0; color: #f07d00; font-family: Open Sans, arial, sans-serif; font-size: 12px; font-weight: bold; text-transform: uppercase;' AS [@style] ,'Some Content' FOR XML PATH ('p); where is the correct position for that? have u a small example for that? thank you! – Sam Enbank Apr 26 '17 at 14:18
@SamEnbank, Uhm... In your question the first few lines show, that you are trying to create something like `
more blah
`. My suggestion with `FOR XML PATH('p')` provides exactly this... I do not know from the given information, where you need this... – Shnugo Apr 26 '17 at 14:25
yes, you are right... i try to create something like
more blah
. What other information do you need? thanks – Sam Enbank Apr 26 '17 at 14:29
@SamEnbank please do not place code in comments... This is hard to read. Rather use the edit option of your question to put this there. Try to reduce your code! Take away everything, which is just *more of the same* and point to the spot, where your issue lives. The code you provide above is not the place, where you are creating any HTML – Shnugo Apr 26 '17 at 14:29
sorry, changed my comment... do you have a short example how to build html code like I have to do? – Sam Enbank Apr 26 '17 at 14:37
@SamEnbank See my update, which does not solve your issue, but will help to clean up your code. If you want to create HTML, you should create XHTML using `FOR XML PATH`. The given information is just not enough to help you... Please read [How to ask a good SQL question](http://meta.stackoverflow.com/questions/271055/tips-for-asking-a-good-structured-query-language-sql-question/271056) and [How to create a MCVE](http://stackoverflow.com/help/mcve) – Shnugo Apr 26 '17 at 14:45
i edit my post with html code which does not work. yes, you are right, my sql code is'nt quite good. I wrote the statemants quickly, because first I want to complete the email body/html. thanks! – Sam Enbank Apr 27 '17 at 06:11
@SamEnbank From your sample I get the impression, that you are trying to create an HTML table. You might want to read [this answer](http://stackoverflow.com/a/39487565/5089204), which offers a fully generic approach to transform any `SELECT` into an HTML table. About your sample: What I'm missing is: **1) How are you building your HTML and 2) which part does not work and 3) what is this *does not work* (error message, wrong output, broken characters...) looking like?** – Shnugo Apr 27 '17 at 06:44
ah, yes... I try to create a HTML table. what do you mean, How am I building the HTML? I save it in a Variable an took this into the email. Everything is working fine, but if I want to insert a Variable, the email will be sent, but the email is blank. without a Variable, the email will be displayed correct. I do not get an error message, there is a wrong output, because the email is blank out of the code before the Variable start. but in the content, it works with the Variable. only in the header it does not work... – Sam Enbank Apr 27 '17 at 09:01
@SamEnbank What do you get, when you write a `SELECT @tableHTML;`? Is the result valid HTML? Try to save this to a file and open it with a browser... – Shnugo Apr 27 '17 at 09:15
I declare the tableHTML as nvarchar(MAX), and set my HTML into the variable, when I do `SELECT @tableHTML;` I get a limited column. not everything is shown in the row... – Sam Enbank Apr 27 '17 at 11:21
ok, I reduced the html. the p-tag with the variable is shown correctly... the variable is shown correct. I saved it as html file and opend it with chrome and worked. why it does not work into an email? :-( – Sam Enbank Apr 27 '17 at 11:54
I tried it with variables in title, that works, in the content, it work, but in is does not work... – Sam Enbank Apr 27 '17 at 12:04
ah ok, I found the mistake... it was the wrong format for the variables... i did `SET @DISPONAME varchar(100)`that was not correct... I did `SET @DISPONAME as char(100)` and the variable is shown correctly in the email. I have a variable for telefonnumber, which format should it be? I tried varchar, char, float, int ... nothing works or should it be other called then char vars? instead of "+" I should try `'%@TELEFON%'`? – Sam Enbank Apr 27 '17 at 13:12
@SamEnbank StackOverflow is not meant to be a discussion board... This is a *Q&A-board*, where experts provide solutions for concrete programming issues. I'd like to stop this here... If my answer and the pushes in comments helped you to solve the **initial issue**, please be so kind to tick the acceptance check below the vote counter. Then start a new question for a new issue. Provide - if possible - a *stand-alone* example to reproduce your problem. This question was very unclear... – Shnugo Apr 28 '17 at 06:22

p tag in ms sql html with @variable

1 Answers1

UPDATE Just a hint: