SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dynamic variables in SQL server for HTML


Dynamic variables in SQL server for HTML

Author
Message
70712
70712
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 25
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
Thom A
Thom A
SSC-Forever
SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)

Group: General Forum Members
Points: 46222 Visits: 15719
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 :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
70712
70712
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 25
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
Thom A
SSC-Forever
SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)

Group: General Forum Members
Points: 46222 Visits: 15719
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 :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
70712
70712
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 25
reading...
70712
70712
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 25
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
Thom A
SSC-Forever
SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)

Group: General Forum Members
Points: 46222 Visits: 15719
[quote]
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 :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
70712
70712
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 25
Thom A - Monday, November 6, 2017 9:31 AM
[quote]
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
Thom A
SSC-Forever
SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)

Group: General Forum Members
Points: 46222 Visits: 15719
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 :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
70712
70712
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 25
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)

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search