SQL Server data to HTML layout_问答_开发者_运维开发者技术经验分享

Hey all, I am trying this code below to be placed into my own HTML layout for export to a PDF.

SELECT 
DISTINCT i.InvoiceNumber as 'Invoice', 
'$' + CONVERT(varchar(50),round((CONVERT(int,Points) * .1),0)) AS 'Amount', 
'$' + CONVERT(varchar(50), 1.50) AS 'Fee' 
FROM tblHGP HGP, OrderDetails OD, tblInvoices i
        JOIN tblCS cs ON i.InvoiceNumber = cs.InvoiceNumber
        JOIN tblEC ac ON i.InvoiceNumber = ac.InvoiceNumber 
WHERE cs.SoldTo = HGP.ECardInd 
AND issued BETWEEN '2010-09-01' AND '2010-09-30 23:59:59' 
AND CountryCode = 'US' 
AND HGP.invoiceNumber = OD.orderdetailsid 
Order by i.InvoiceNumber

Right now it returns 6 records and i need to be able to create a HTML for EACH record it finds (and in this example, 6)

This is some of my HTML code:

SET @theHTML= '<html><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<style type="text/css"><!--
span.cls_002{font-family:Arial,serif;font-size:15.6px;color:rgb(0,0,0);font-weight:bold;font-style:normal}
div.cls_002{font-family:Arial,serif;font-size:13.6px;color:rgb(0,0,0);font-weight:bold;font-style:normal}
....
<div style="position:absolute; left:90.00px; top:313.08px; width: 249px" class="cls_004">' + @whatReportFees + '</div>
<div style="position:absolute; left:347px; top:313.32px" class="cls_004">' + @FeesQty + '</div>
<div style="position:absolute; left:424.44px; top:313.32px" class="cls_004">$1.50</div>
<div style="position:absolute; left:482.89px; top:313.32px" class="cls_004">$' + convert(varchar(30), CONVERT(MONEY, @FeesTotal, 3), 3) + '</div>
<div style="position:absolute; left:90px; top:329px; width: 249px" class="cls_004">' + @whatReportRed + '</div>
 etc...

I don't really know how to incorporate the HTML into the query so that it creates a new one every time it finds a new invoice number. The whatReportRed, FeesQty would be where the data would need to go.

Then I would run this to crate the PDF

 exec clrPdfFromHTML '\\reports\report.pdf', @theHTML

Any input would be great! :o)

TESTING

DECLARE @HtmlHead varchar(4000); SET @HtmlHead = '<html><head></head><body>'
DECLARE @HtmlTail varchar(4000); SET @HtmlTail = '</body></html>'

DECLARE @theHTML varchar(4000)
DECLARE CreatePdf_Curs开发者_JS百科or CURSOR FOR
SELECT @HtmlHead
    + '<div>' + i.InvoiceNumber + '</div>'
    + @HtmlTail AS theHTML
FROM 
    (SELECT 
    DISTINCT i.InvoiceNumber as 'Invoice', 
    '$' + CONVERT(varchar(50),round((CONVERT(int,Points) * .1),0)) AS 'Amount', 
    '$' + CONVERT(varchar(50), 1.50) AS 'Fee' 
    FROM tblHGP HGP, OrderDetails OD, tblInvoices i
    JOIN tblCS cs ON i.InvoiceNumber = cs.InvoiceNumber
    JOIN tblEC ac ON i.InvoiceNumber = ac.InvoiceNumber 
     WHERE cs.SoldTo = HGP.ECardInd 
    AND issued BETWEEN '2010-09-01' AND '2010-09-30 23:59:59' 
    AND CountryCode = 'US' 
    AND HGP.invoiceNumber = OD.orderdetailsid)
OPEN CreatePdf_Cursor
WHILE 1=1
BEGIN
    FETCH NEXT FROM CreatePdf_Cursor INTO @theHTML
    IF @@FETCH_STATUS <> 0 BREAK
    --exec clrPdfFromHTML '\\reports\report.pdf', @theHTML
END
CLOSE CreatePdf_Cursor
DEALLOCATE CreatePdf_Cursor

ERROR

Msg 156, Level 15, State 1, Line 21

Incorrect syntax near the keyword 'OPEN'.

David

The code you added under TESTING should read:

DECLARE @HtmlHead varchar(4000); SET @HtmlHead = '<html><head></head><body>'
DECLARE @HtmlTail varchar(4000); SET @HtmlTail = '</body></html>'

DECLARE @theHTML varchar(4000)
DECLARE CreatePdf_Cursor CURSOR FOR
SELECT @HtmlHead
    + '<div>' + Cast(mySubquery.Invoice as varchar(100)) + '</div>'
    + @HtmlTail AS theHTML
FROM 
    (SELECT 
    DISTINCT i.InvoiceNumber as 'Invoice', 
    '$' + CONVERT(varchar(50),round((CONVERT(int,Points) * .1),0)) AS 'Amount', 
    '$' + CONVERT(varchar(50), 1.50) AS 'Fee' 
    FROM tblHGP HGP, OrderDetails OD, tblInvoices i
    JOIN tblCS cs ON i.InvoiceNumber = cs.InvoiceNumber
    JOIN tblEC ac ON i.InvoiceNumber = ac.InvoiceNumber 
     WHERE cs.SoldTo = HGP.ECardInd 
    AND issued BETWEEN '2010-09-01' AND '2010-09-30 23:59:59' 
    AND CountryCode = 'US' 
    AND HGP.invoiceNumber = OD.orderdetailsid) as mySubquery
OPEN CreatePdf_Cursor
WHILE 1=1
BEGIN
    FETCH NEXT FROM CreatePdf_Cursor INTO @theHTML
    IF @@FETCH_STATUS <> 0 BREAK
    --exec clrPdfFromHTML '\\reports\report.pdf', @theHTML
END
CLOSE CreatePdf_Cursor
DEALLOCATE CreatePdf_Cursor

Calling a stored procedure for each row in a result set really calls for a cursor, its one of the few things you can't do with set based SQL unless you can pull apart the stored procedure.

To use a simpler example:

DECLARE @HtmlHead varchar(4000); SET @HtmlHead = '<html><head></head><body>'
DECLARE @HtmlTail varchar(4000); SET @HtmlTail = '</body></html>'

DECLARE @theHTML varchar(4000)
DECLARE CreatePdf_Cursor CURSOR FOR
    SELECT @HtmlHead
        + '<div>' + col1 + '</div>'
        + @HtmlTail AS theHTML
    FROM table
OPEN CreatePdf_Cursor
WHILE 1=1
BEGIN
    FETCH NEXT FROM CreatePdf_Cursor INTO @theHTML
    IF @@FETCH_STATUS <> 0 BREAK

    exec clrPdfFromHTML '\\reports\report.pdf', @theHTML
END
CLOSE CreatePdf_Cursor
DEALLOCATE CreatePdf_Cursor

With cursors like this I prefer to break in the middle of an infinite loop rather than repeating the FETCH NEXT code.