Dynamic variables in SQL server for HTML

Question

Dynamic variables in SQL server for HTML

70712

Old Hand

Points: 347
More actions
November 6, 2017 at 4:35 am

#341193

I am trying to recreate and automated email. The email has gone from having, one column with headers and one column with results to one column with headers and anywhere between 1 and 10 result columns. I have a dynamic variable in SQL Server that I am trying to pass to HTML to create an email using the data from a subquery.
The code to create the dynamic reference looks like this:
--A variable to hold the complete string
DECLARE @Columns VARCHAR(MAX) = ''
--Concatenate each with a comma
SELECT @Columns += '+''<TD>'' + u.' + (QUOTENAME(Child) + ' +''</TD>''')FROM #pnt_invoiceStream_arch
--Remove the trailing comma
SET @Columns = right(@Columns, LEN(@Columns)-2)
The results of this query, look like this:
<TD>' + u.[1] +'</TD>'+'<TD>' + u.[2] +'</TD>'+'<TD>' + u.[3] +'</TD>'+'<TD>' + u.[4] +'</TD>'+'<TD>' + u.[5] +'</TD>'+'<TD>' + u.[6] +'</TD>'+'<TD>' + u.[7] +'</TD>'
The HTML Code I am trying to recreate is this:
SET @resultsHTML = '<TABLE>'SELECT @resultsHTML = @resultsHTML + '<TR><TD>' + u.Header +'</TD>'+ '<TD>' + u.[1] + '</TD>' +'</TR>'
And with the variable looks like this:
SELECT @resultsHTML = @resultsHTML + '<TR><TD>' + u.Header +'</TD>' + @Columns+'</TR>'
The original email looks like this (e.g.):
Bill Run: xxxxxx
Invoice Date: 2017-07-21
Invoice Count: 1078
Billed Days: 332823
Min Invoice Due: £xxxx.xx
Max Invoice Due: £xxxx.xx
Total Current Invoice Charges (inc. VAT): £xxxx.xx
Total Due (inc. VAT): £xxxx.xx
VDD Payment Date: 2017-08-18
I was expecting the email with the variable added to look like this(e.g.):
Bill Run: xxxxxx xxxxxx
Invoice Date: 2017-07-21 2017-08-16
Invoice Count: 1078 1083
Billed Days: 332823 338388
Min Invoice Due: £-xxxx.xx £-xxxx.xx
Max Invoice Due: £xxxx.xx £xxxx.xx
Total Current Invoice Charges (inc. VAT): £xxxx.xx £xxxxx.xx
Total Due (inc. VAT): £xxxx.xx £xxxx.xx
VDD Payment Date: 2017-10-25 2017-10-25
Report: http://dc1-srv-dm02/ReportServer/Pages/ReportViewer.aspx?/CPMDAT/LogNet/Billed+Finance+Report&gen_id=312334
With slightly better formatting for the columns.
However the results are very different and look like this (e.g.):
Bill Run: ' + u.[1] +' '+' ' + u.[2] +' '+' ' + u.[3] +' '+' ' + u.[4] +' '+' ' + u.[5] +' '
Invoice Date: ' + u.[1] +' '+' ' + u.[2] +' '+' ' + u.[3] +' '+' ' + u.[4] +' '+' ' + u.[5] +' '
Invoice Count: ' + u.[1] +' '+' ' + u.[2] +' '+' ' + u.[3] +' '+' ' + u.[4] +' '+' ' + u.[5] +' '
Billed Days: ' + u.[1] +' '+' ' + u.[2] +' '+' ' + u.[3] +' '+' ' + u.[4] +' '+' ' + u.[5] +' '
Min Invoice Due: ' + u.[1] +' '+' ' + u.[2] +' '+' ' + u.[3] +' '+' ' + u.[4] +' '+' ' + u.[5] +' '
Max Invoice Due: ' + u.[1] +' '+' ' + u.[2] +' '+' ' + u.[3] +' '+' ' + u.[4] +' '+' ' + u.[5] +' '
Total Current Invoice Charges (inc. VAT): ' + u.[1] +' '+' ' + u.[2] +' '+' ' + u.[3] +' '+' ' + u.[4] +' '+' ' + u.[5] +' '
Total Due (inc. VAT): ' + u.[1] +' '+' ' + u.[2] +' '+' ' + u.[3] +' '+' ' + u.[4] +' '+' ' + u.[5] +' '
VDD Payment Date: ' + u.[1] +' '+' ' + u.[2] +' '+' ' + u.[3] +' '+' ' + u.[4] +' '+' ' + u.[5] +' '
I'm not sure why this is happening, hoping I can get some advice on how to make the HTML code pick up the Variable. Of course it has to be dynamic as I won't know how many columns need to show for any given email.
Not sure if I've missed anything but if anything is unclear feel free to let me know.
Thanks

Viewing 15 posts - 1 through 15 (of 25 total)

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

Thom A SSC Guru Points: 99130 More actions · Answer 1

Those links are to your local server, they aren't going to be accessible by us; we're not on your network.

This would be easier with some consumable sample data and a demonstration of what you want it to look like. Considering you said that the number of columns will be dynamic, it'll be good if you supply a couple of samples (both data and results) so that we can see how it should look for different eventualities.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

70712 Old Hand Points: 347 More actions · Answer 2

Hi,

Apologies, this is what the email should look like:

My intention is to use the variable to identify, how many columns the email would need to show. For example, this would be u.1, u.2, u.3, u.4 within the above code snippets. Not sure I can provide you with sample data.

However when using the variable, as I have it, currently the email looks like this:

Thom A SSC Guru Points: 99130 More actions · Answer 3

70712 - Monday, November 6, 2017 7:48 AM
Hi,
Apologies, this is what the email looks like:
I am using the variable to identify, how many columns the email would need to show. For example, this would be u.1, u.2, u.3, u.4 within the above code snippets. Not sure I can provide you with sample data.

Thanks, but we need some data we can work with. Have a look at the link in my signature, that explains how to post sample data. Then, also, supply what you want the expected output to look like. As i said, we're going to need a couple of scenarios here, so that we can work out what it is you want.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

70712 Old Hand Points: 347 More actions · Answer 4

70712

Old Hand

Points: 347

November 6, 2017 at 8:07 am

#1966796

reading...

70712 Old Hand Points: 347 More actions · Answer 5

Thom A - Monday, November 6, 2017 7:54 AM
70712 - Monday, November 6, 2017 7:48 AM
Hi,
Apologies, this is what the email looks like:
I am using the variable to identify, how many columns the email would need to show. For example, this would be u.1, u.2, u.3, u.4 within the above code snippets. Not sure I can provide you with sample data.
Thanks, but we need some data we can work with. Have a look at the link in my signature, that explains how to post sample data. Then, also, supply what you want the expected output to look like. As i said, we're going to need a couple of scenarios here, so that we can work out what it is you want.

Hi,

Some test data bellow if I have understood those guidelines correctly. As I say, I'm able to create the email from this data, however, it is the dynamic variable that I am trying to pass to HTML via SQL server (If that makes sense) where I am having trouble.

IF OBJECT_ID('tempdb..#test_data','U') IS NOT NULL DROP TABLE #test_data

--SET arithabort OFF;

CREATE TABLE #test_data
    (
BillRun INT
,bill_date SMALLDATETIME
,invoice_count INT
,BilledDays FLOAT
,AgedBilledDays FLOAT
,MinInvoiceDue VARCHAR(150)
,MaxInvoiceDue VARCHAR(150)
,TotalCurrentInvoiceChargesIncVat VARCHAR(150)
,TotalDueIncVat VARCHAR(150)
,VDDPaymentDate SMALLDATETIME
,Child VARCHAR(150)
    )

insert into #test_data values ('369873',CURRENT_TIMESTAMP,'6','3047','27390','-261.24','0.03','1901.24','-417.61',CURRENT_TIMESTAMP,'1');
insert into #test_data values ('369874',CURRENT_TIMESTAMP,'6','2181','27390','-283.24','0.03','1623.24','-834.61',CURRENT_TIMESTAMP,'2');
insert into #test_data values ('369876',CURRENT_TIMESTAMP,'5','6089','27390','-149.89','2218.31','3059.52','2039.23',CURRENT_TIMESTAMP,'4');
insert into #test_data values ('369875',CURRENT_TIMESTAMP,'3','2931','27390','-184.63','4270.84','4379.66','6438',CURRENT_TIMESTAMP,'3');
insert into #test_data values ('369877',CURRENT_TIMESTAMP,'9','5126','27390','-384.76','2403.05','3477.56','3079.47',CURRENT_TIMESTAMP,'5');
insert into #test_data values ('369878',CURRENT_TIMESTAMP,'11','3485','27390','-577.76','3108.78','7870.38','4432.59',CURRENT_TIMESTAMP,'6');
insert into #test_data values ('369879',CURRENT_TIMESTAMP,'1','855','27390','605.27','605.27','159.95','605.27',CURRENT_TIMESTAMP,'7');

select * from #test_data

(With #test_data being representative of #pnt_invoiceStream_arch in the original post)

Thom A SSC Guru Points: 99130 More actions · Answer 6

70712 - Monday, November 6, 2017 9:20 AM

Hi,

Some test data bellow if I have understood those guidelines correctly. As I say, I'm able to create the email from this data, however, it is the dynamic variable that I am trying to pass to HTML via SQL server (If that makes sense) where I am having trouble.

Thanks for that.

I've tried running your code with the same data, however, this made me notice that your syntax for setting the value of @resultsHTML isn't valid, so I couldn't. Is this the part your speaking about? I'm also not sure what you mean by pass a variable to HTML? Are you trying to pass a value to a web server? Do you mean to create a hyperlink to something? For example, when you pass a parameter to a webpage, this is often done by adding it to the end of the web address. For example http://www.mywebsite.com/index.html?name=Thom_A passes a parameter name to the page, with the value Thom_A.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

70712 Old Hand Points: 347 More actions · Answer 7

Thom A - Monday, November 6, 2017 9:31 AM

70712 - Monday, November 6, 2017 9:20 AM
Hi,
Some test data bellow if I have understood those guidelines correctly. As I say, I'm able to create the email from this data, however, it is the dynamic variable that I am trying to pass to HTML via SQL server (If that makes sense) where I am having trouble.

Thanks for that.

I've tried running your code with the same data, however, this made me notice that your syntax for setting the value of @resultsHTML isn't valid, so I couldn't. Is this the part your speaking about? I'm also not sure what you mean by pass a variable to HTML? Are you trying to pass a value to a web server? Do you mean to create a hyperlink to something? For example, when you pass a parameter to a webpage, this is often done by adding it to the end of the web address. For example http://www.mywebsite.com/index.html?name=Thom_A passes a parameter name to the page, with the value Thom_A.

So I'm creating the variable @Columns which should then inform how many columns should be in the email. But the HTML code creating the table/email only pastes the contents of the variable. I have two different versions of the @resultsHTML the second one contains the variable which is causing me a problem.

Now that I think about it, I hope that what I'm trying to do is possible in the first instance.

Thanks for your help thus far.

Thom A SSC Guru Points: 99130 More actions · Answer 8

Maybe it'll be easier for me to start from the beginning instead and simply understand your end goal, rather than what it is you've tried so far. At the moment you have the data, which you've suipplied in a consumable format (thanks for that!). Now you want to create an email which, I think, should contain something like this..?

<table border="1">                          
  <tr>                            
   <td>BillRun</td>
   <td>369873</td>
   <td>369874</td>
   <td>369876</td>
   <td>369875</td>
   <td>369877</td>   
   <td>369878</td>   
   <td>369879</td>
  </tr>
  <tr>
   <td>bill_date</td>   
   <td>43045.6868055556</td>   
   <td>43045.6868055556</td>   
   <td>43045.6868055556</td>   
   <td>43045.6868055556</td>   
   <td>43045.6868055556</td>   
   <td>43045.6868055556</td>   
   <td>43045.6868055556</td>
  </tr>
  <tr>
   <td>invoice_count</td>   
   <td>6</td>   
   <td>6</td>   
   <td>5</td>   
   <td>3</td>   
   <td>9</td>   
   <td>11</td>   
   <td>1</td>
  </tr>
  <tr>
   <td>BilledDays</td>   
   <td>3047</td>   
   <td>2181</td>   
   <td>6089</td>   
   <td>2931</td>   
   <td>5126</td>   
   <td>3485</td>   
   <td>855</td>
  </tr>
  <tr>
   <td>AgedBilledDays</td>   
   <td>27390</td>   
   <td>27390</td>   
   <td>27390</td>   
   <td>27390</td>   
   <td>27390</td>   
   <td>27390</td>   
   <td>27390</td>
  </tr>
  <tr>   
   <td>MinInvoiceDue</td>   
   <td>-261.24</td>   
   <td>-283.24</td>   
   <td>-149.89</td>   
   <td>-184.63</td>   
   <td>-384.76</td>   
   <td>-577.76</td>   
   <td>605.27</td>
  </tr>
  <tr>   
   <td>MaxInvoiceDue</td>   
   <td>0.03</td>   
   <td>0.03</td>   
   <td>2218.31</td>   
   <td>4270.84</td>   
   <td>2403.05</td>   
   <td>3108.78</td>   
   <td>605.27</td>
  </tr>
  <tr>   
   <td>TotalCurrentInvoiceChargesIncVat</td>   
   <td>1901.24</td>   
   <td>1623.24</td>   
   <td>3059.52</td>   
   <td>4379.66</td>   
   <td>3477.56</td>   
   <td>7870.38</td>   
   <td>159.95</td>
  </tr>
  <tr>   
   <td>TotalDueIncVat</td>   
   <td>-417.61</td>   
   <td>-834.61</td>   
   <td>2039.23</td>   
   <td>6438</td>   
   <td>3079.47</td>   
   <td>4432.59</td>   
   <td>605.27</td>
  </tr>
  <tr>   
   <td>VDDPaymentDate</td>   
   <td>43045.6868055556</td>   
   <td>43045.6868055556</td>   
   <td>43045.6868055556</td>   
   <td>43045.6868055556</td>   
   <td>43045.6868055556</td>   
   <td>43045.6868055556</td>   
   <td>43045.6868055556</td>
  </tr>
</table>

This results in a table looking somewhat like this:

Is my understanding correct?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

70712 Old Hand Points: 347 More actions · Answer 9

Thom A - Monday, November 6, 2017 9:57 AM
Maybe it'll be easier for me to start from the beginning instead and simply understand your end goal, rather than what it is you've tried so far. At the moment you have the data, which you've suipplied in a consumable format (thanks for that!). Now you want to create an email which, I think, should contain something like this..?
<table border="1">
<tr>
   <td>BillRun</td>
   <td>369873</td>
   <td>369874</td>
   <td>369876</td>
   <td>369875</td>
   <td>369877</td>
   <td>369878</td>
   <td>369879</td>
</tr>
<tr>
   <td>bill_date</td>
   <td>43045.6868055556</td>
   <td>43045.6868055556</td>
   <td>43045.6868055556</td>
   <td>43045.6868055556</td>
   <td>43045.6868055556</td>
   <td>43045.6868055556</td>
   <td>43045.6868055556</td>
</tr>
<tr>
   <td>invoice_count</td>
   <td>6</td>
   <td>6</td>
   <td>5</td>
   <td>3</td>
   <td>9</td>
   <td>11</td>
   <td>1</td>
</tr>
<tr>
   <td>BilledDays</td>
   <td>3047</td>
   <td>2181</td>
   <td>6089</td>
   <td>2931</td>
   <td>5126</td>
   <td>3485</td>
   <td>855</td>
</tr>
<tr>
   <td>AgedBilledDays</td>
   <td>27390</td>
   <td>27390</td>
   <td>27390</td>
   <td>27390</td>
   <td>27390</td>
   <td>27390</td>
   <td>27390</td>
</tr>
<tr>
   <td>MinInvoiceDue</td>
   <td>-261.24</td>
   <td>-283.24</td>
   <td>-149.89</td>
   <td>-184.63</td>
   <td>-384.76</td>
   <td>-577.76</td>
   <td>605.27</td>
</tr>
<tr>
   <td>MaxInvoiceDue</td>
   <td>0.03</td>
   <td>0.03</td>
   <td>2218.31</td>
   <td>4270.84</td>
   <td>2403.05</td>
   <td>3108.78</td>
   <td>605.27</td>
</tr>
<tr>
   <td>TotalCurrentInvoiceChargesIncVat</td>
   <td>1901.24</td>
   <td>1623.24</td>
   <td>3059.52</td>
   <td>4379.66</td>
   <td>3477.56</td>
   <td>7870.38</td>
   <td>159.95</td>
</tr>
<tr>
   <td>TotalDueIncVat</td>
   <td>-417.61</td>
   <td>-834.61</td>
   <td>2039.23</td>
   <td>6438</td>
   <td>3079.47</td>
   <td>4432.59</td>
   <td>605.27</td>
</tr>
<tr>
   <td>VDDPaymentDate</td>
   <td>43045.6868055556</td>
   <td>43045.6868055556</td>
   <td>43045.6868055556</td>
   <td>43045.6868055556</td>
   <td>43045.6868055556</td>
   <td>43045.6868055556</td>
   <td>43045.6868055556</td>
</tr>
</table>
This results in a table looking somewhat like this:

Is my understanding correct?

Yes, that's correct. the data comes from SQL and is being pivoted before creating a HTML table to paste into an email.
I won't know how many columns I need to create so am trying to create a variable to inform that. (Hope I'm explaining properly)

Thom A SSC Guru Points: 99130 More actions · Answer 10

OK, I see what you're after now.

SQL isn't really great at this, if I'm going to be honest. If you're generating HTML reports it's best at top-down reports, not left-right. I'll have a think on a solution, but earliest I'll be able to post back will be tomorrow.

In the mean time, is there any reason why you can't do top-down? That would be FAR FAR easier. Otherwise we're going to have to start mixing data type, and the solution is not going to be exactly... pretty.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

70712 Old Hand Points: 347 More actions · Answer 11

Thom A - Monday, November 6, 2017 10:15 AM
OK, I see what you're after now.
SQL isn't really great at this, if I'm going to be honest. If you're generating HTML reports it's best at top-down reports, not left-right. I'll have a think on a solution, but earliest I'll be able to post back will be tomorrow.
In the mean time, is there any reason why you can't do top-down? That would be FAR FAR easier. Otherwise we're going to have to start mixing data type, and the solution is not going to be exactly... pretty.

I guess it's a formatting/end user issue. Hence the use of pivots etc to get it to the point where it can spit out an email in the format I've shown.
But incorporating a variable using SQL seems to be the sticking point.
Tomorrow is absolutely fine. Thank you for taking the time at all.

Thom A SSC Guru Points: 99130 More actions · Answer 12

Ok, I firstly wanted to show you how much EASIER this really is doing the table top down. This took me about 10 minutes this morning for me to do. If you have to pivot the table, then I'll see what we can do, but it's go to take far longer than 10 minutes (so i'm little opposed to doing so), and is going to require dynamic SQL.

Anyway, the top-down SQL:

--DECLARE variable
DECLARE @TableHTML nvarchar(MAX);
--Start the table and Table Header HTML
SET @TableHTML = N'<table border="1">' + NCHAR(10) +
 N'<tr>' + NCHAR(10) +
 N'<th>BillRun</th>' + NCHAR(10) +
      N'<th>Bill Date</th>' + NCHAR(10) +
 N'<th>Invoice Count</th>' + NCHAR(10) +
 N'<th>Billed Days</th>' + NCHAR(10) +
 N'<th>Aged Billed Days</th>' + NCHAR(10) +
 N'<th>Min Invoice Due</th>' + NCHAR(10) +
 N'<th>Max Invoice Due</th>' + NCHAR(10) +
 N'<th>Total Current Invoice Charges (Inc VAT)</th>' + NCHAR(10) +
 N'<th>Total Due (Inc VAT)</th>' + NCHAR(10) +
 N'<th>VDD Payment Date</th>' + NCHAR(10) +
 N'</tr>';
--Put the data in the table.
SET @TableHTML = @TableHTML + CONVERT(nvarchar(MAX),
  (SELECT td = BillRun,N'' + NCHAR(10),
     td = CONVERT(nvarchar(15),bill_date,106), N'' + NCHAR(10),
     td = invoice_count,N'' + NCHAR(10),
     td = BilledDays,N'' + NCHAR(10),
     td = AgedBilledDays,N'' + NCHAR(10),
     td = MinInvoiceDue,N'' + NCHAR(10),
     td = MaxInvoiceDue,N'' + NCHAR(10),
     td = TotalCurrentInvoiceChargesIncVat,N'' + NCHAR(10),
     td = TotalDueIncVat,N'' + NCHAR(10),
     td = CONVERT(nvarchar(15),VDDPaymentDate,106),N'' + NCHAR(10)
   FROM #test_data
   FOR XML PATH('tr')));
--Add the Table end tag
SET @TableHTML = @TableHTML + N'</table>';
/*
Replace the escape code for the new line.
This is purely for asphetics, the NCHAR(10) and this replace aren't needed, but
it makes the HTML a lot easier to read when you PRINT it.
*/
SET @TableHTML = REPLACE(@TableHTML, N'
',NCHAR(10));
--PRINT the HTML as I'm not sending any kind of email, etc.
PRINT @TableHTML;

If this isn't something you can entertain, let me know, but like I said before, this is a much more preferred solution.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

70712 Old Hand Points: 347 More actions · Answer 13

Thom A - Tuesday, November 7, 2017 2:05 AM
Ok, I firstly wanted to show you how much EASIER this really is doing the table top down. This took me about 10 minutes this morning for me to do. If you have to pivot the table, then I'll see what we can do, but it's go to take far longer than 10 minutes (so i'm little opposed to doing so), and is going to require dynamic SQL.
Anyway, the top-down SQL:
--DECLARE variable
DECLARE @TableHTML nvarchar(MAX);
--Start the table and Table Header HTML
SET @TableHTML = N'<table border="1">' + NCHAR(10) +
N'<tr>' + NCHAR(10) +
N'<th>BillRun</th>' + NCHAR(10) +
      N'<th>Bill Date</th>' + NCHAR(10) +
N'<th>Invoice Count</th>' + NCHAR(10) +
N'<th>Billed Days</th>' + NCHAR(10) +
N'<th>Aged Billed Days</th>' + NCHAR(10) +
N'<th>Min Invoice Due</th>' + NCHAR(10) +
N'<th>Max Invoice Due</th>' + NCHAR(10) +
N'<th>Total Current Invoice Charges (Inc VAT)</th>' + NCHAR(10) +
N'<th>Total Due (Inc VAT)</th>' + NCHAR(10) +
N'<th>VDD Payment Date</th>' + NCHAR(10) +
N'</tr>';
--Put the data in the table.
SET @TableHTML = @TableHTML + CONVERT(nvarchar(MAX),
(SELECT td = BillRun,N'' + NCHAR(10),
     td = CONVERT(nvarchar(15),bill_date,106), N'' + NCHAR(10),
     td = invoice_count,N'' + NCHAR(10),
    td = BilledDays,N'' + NCHAR(10),
    td = AgedBilledDays,N'' + NCHAR(10),
    td = MinInvoiceDue,N'' + NCHAR(10),
    td = MaxInvoiceDue,N'' + NCHAR(10),
    td = TotalCurrentInvoiceChargesIncVat,N'' + NCHAR(10),
    td = TotalDueIncVat,N'' + NCHAR(10),
    td = CONVERT(nvarchar(15),VDDPaymentDate,106),N'' + NCHAR(10)
FROM #test_data
FOR XML PATH('tr')));
--Add the Table end tag
SET @TableHTML = @TableHTML + N'</table>';
/*
Replace the escape code for the new line.
This is purely for asphetics, the NCHAR(10) and this replace aren't needed, but
it makes the HTML a lot easier to read when you PRINT it.
*/
SET @TableHTML = REPLACE(@TableHTML, N' ',NCHAR(10));
--PRINT the HTML as I'm not sending any kind of email, etc.
PRINT @TableHTML;
If this isn't something you can entertain, let me know, but like I said before, this is a much more preferred solution.

Hey Thom,

Thanks for the help, top down isn't really doable as it has to be easily imported elsewhere and that format won't work for that. I think I've had some success with the pivot element however it is the dynamic part which is creating a problem for me.

70712 Old Hand Points: 347 More actions · Answer 14

70712 - Tuesday, November 7, 2017 2:34 AM
Thom A - Tuesday, November 7, 2017 2:05 AM
Ok, I firstly wanted to show you how much EASIER this really is doing the table top down. This took me about 10 minutes this morning for me to do. If you have to pivot the table, then I'll see what we can do, but it's go to take far longer than 10 minutes (so i'm little opposed to doing so), and is going to require dynamic SQL.
Anyway, the top-down SQL:
--DECLARE variable
DECLARE @TableHTML nvarchar(MAX);
--Start the table and Table Header HTML
SET @TableHTML = N'<table border="1">' + NCHAR(10) +
N'<tr>' + NCHAR(10) +
N'<th>BillRun</th>' + NCHAR(10) +
      N'<th>Bill Date</th>' + NCHAR(10) +
N'<th>Invoice Count</th>' + NCHAR(10) +
N'<th>Billed Days</th>' + NCHAR(10) +
N'<th>Aged Billed Days</th>' + NCHAR(10) +
N'<th>Min Invoice Due</th>' + NCHAR(10) +
N'<th>Max Invoice Due</th>' + NCHAR(10) +
N'<th>Total Current Invoice Charges (Inc VAT)</th>' + NCHAR(10) +
N'<th>Total Due (Inc VAT)</th>' + NCHAR(10) +
N'<th>VDD Payment Date</th>' + NCHAR(10) +
N'</tr>';
--Put the data in the table.
SET @TableHTML = @TableHTML + CONVERT(nvarchar(MAX),
(SELECT td = BillRun,N'' + NCHAR(10),
     td = CONVERT(nvarchar(15),bill_date,106), N'' + NCHAR(10),
     td = invoice_count,N'' + NCHAR(10),
    td = BilledDays,N'' + NCHAR(10),
    td = AgedBilledDays,N'' + NCHAR(10),
    td = MinInvoiceDue,N'' + NCHAR(10),
    td = MaxInvoiceDue,N'' + NCHAR(10),
    td = TotalCurrentInvoiceChargesIncVat,N'' + NCHAR(10),
    td = TotalDueIncVat,N'' + NCHAR(10),
    td = CONVERT(nvarchar(15),VDDPaymentDate,106),N'' + NCHAR(10)
FROM #test_data
FOR XML PATH('tr')));
--Add the Table end tag
SET @TableHTML = @TableHTML + N'</table>';
/*
Replace the escape code for the new line.
This is purely for asphetics, the NCHAR(10) and this replace aren't needed, but
it makes the HTML a lot easier to read when you PRINT it.
*/
SET @TableHTML = REPLACE(@TableHTML, N' ',NCHAR(10));
--PRINT the HTML as I'm not sending any kind of email, etc.
PRINT @TableHTML;
If this isn't something you can entertain, let me know, but like I said before, this is a much more preferred solution.
Hey Thom,
Thanks for the help, top down isn't really doable as it has to be easily imported elsewhere and that format won't work for that. I think I've had some success with the pivot element however it is the dynamic part which is creating a problem for me.

So at the moment I can do everything apart from get the html created email to recognise my variable as a block of code rather than text. Meaning that as it stands I would have to go in each time and manually add or remove lines relative to how many columns I wanted in my table.