ansaurus

Question

Answer 1

+1 A:

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.

webturner 2010-10-25 15:14:50

Of course you need to make report.pdf dynamic, otherwise it will be overwritten each row.

webturner 2010-10-25 15:20:50

Where would the basic query that i have in my OP be placed within your query above, @webturner?

StealthRT 2010-10-25 15:34:07

Replace the SELECT statement between declaring the cursor and opening it. Remember it needs to return a single column which is the entire @theHTML value.

webturner 2010-10-25 15:39:48

Has errors:Msg 156, Level 15, State 1, Line 7Incorrect syntax near the keyword 'DECLARE'. **DECLARE CreatePdf_Cursor CURSOR FOR**Msg 137, Level 15, State 2, Line 16Must declare the scalar variable "@theHTML". **FETCH NEXT FROM CreatePdf_Cursor INTO @theHTML**

StealthRT 2010-10-25 15:42:28

Edited, I forgot the datatype for @theHTML

webturner 2010-10-25 15:45:58

Check my OP, @webturner

StealthRT 2010-10-25 15:59:59

You need to give the subquery a name. i.e. line 20 ends "OD.orderdetailsid) AS I"

webturner 2010-10-25 16:15:51

Answer 2

+1 A:

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

webturner 2010-10-25 16:18:57

Ok, i corrected that but i am getting the error **Msg 245, Level 16, state 1, Line 21 Conversion failed when converting the varchar value '<html><head></head><body><div>' to data type int** on **OPEN CreatePdf_Cursor**

StealthRT 2010-10-25 16:21:58

Ok I've cast the int to a varchar now. You should really be able to figure out all these little tweaks to the example for yourself. This is not the place to have someone do your work for you.

webturner 2010-10-25 16:34:36

Thanks for the help, webturner. I'm new to MSSQL and learning by example as i go.

StealthRT 2010-10-25 16:47:14

ansaurus

tags:

views:

answers:

SQL Server data to HTML layout

related questions