Insert parameter into HTML using sql

Question

I have a sql job which runs once every 24 hours and sends an email with the data requested from my database tables. I'm inserting the data into :

DECLARE @ReportContentBuilder   table(LineSequence int identity, Line varchar(2000))

Like so :

INSERT INTO @ReportContentBuilder VALUES('<html>')
INSERT INTO @ReportContentBuilder VALUES('<head>')
INSERT INTO @ReportContentBuilder VALUES('<style type="text/css">')
INSERT INTO @ReportContentBuilder VALUES('body{font-family: Calibri; font-size: 10pt;}')
INSERT INTO @ReportContentBuilder VALUES('</style>')
INSERT INTO @ReportContentBuilder VALUES('</head>')
INSERT INTO @ReportContentBuilder VALUES('<body>')

Which is all good the email is sent and I receive all the data as expected. Its just my table headings are all the same.

WHILE (@MessageTypeCount > 0)
BEGIN
-- SET a parameter here to use as Id
    INSERT INTO @ReportContentBuilder VALUES('<table cellpadding="2" border="1">')
    INSERT INTO @ReportContentBuilder VALUES('<caption><b>Id</b></caption>')

-- Etc. etc. all the rest of the code works
END

I have a While loop and want to pass in a parameter into the Id section so the table heading will be different for each loop.

Question:

How do I pass a value into the Html part of the code to receive different table headings in my email?

I hope this is clear and I've added enough info.

Alex Szabo · Accepted Answer · 2014-12-01 12:36:42Z

1

If I get this right, this will help (assuming MSSQL):

DECLARE @Id AS INT
SET @Id = 1

WHILE (@MessageTypeCount > 0)
BEGIN
-- SET a parameter here to use as Id

    INSERT INTO @ReportContentBuilder VALUES('<table cellpadding="2" border="1">')
    INSERT INTO @ReportContentBuilder VALUES('<caption><b>' + cast(@Id as VARCHAR(3)) + '</b></caption>')

    SET @Id = @Id + 1 /* Or SET @Id += 1 */
-- Etc. etc. all the rest of the code works
END

The variable @Id will serve as an example, where you will differentiate each run by an auto_increment value. In the second insert, you will break the literal string, concatenate it with the varchar value of @Id (if you don't convert or cast it, it might try to add a number to a string, and that'll surely break the script), and add the rest of the HTML after the Id.

answered Dec 1, 2014 at 12:36

Alex Szabo

3,2842 gold badges21 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Insert parameter into HTML using sql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related