March 23, 2015 at 5:39 am
I did something similar, and had a helpful hint from something that was posted here on SSC (Mr Magoo I think!!)
Basically this was the code I implemented to create a HTML table for email purposes, the field names are irrelevant should give you some pointers.
/*
CREATE Table Header with column names
*/
SET @TableHeader =
(
SELECT
'background-color:#6495ED;font-weight:bold' as [@Style]
, 'Movement Date'AS Th
, ''AS [*]
, 'YOA'AS Th
, ''AS [*]
, 'Risk Ref'AS Th
, ''AS [*]
, 'Insured'AS Th
, ''AS [*]
, 'ClaimRef'AS Th
, ''AS [*]
, 'UCR'AS Th
, ''AS [*]
, 'Incurred (GBP) Prev'AS Th
, ''AS [*]
, 'Movement OS (GBP)'AS Th
, ''AS [*]
, 'Movement Paid'AS Th
, ''AS [*]
, 'Incurred (GBP) Curr' AS Th
FOR XML PATH('tr')
)
/*
CREATE Table Row from data set
*/
SET @TableRows=
(
SELECT
'background-color:#FFF8C6;font-weight:normal'AS [@style]
, MovementDateAS Td
, ''AS [*] --Column delimiter
, YearOfAccountAS Td
, ''AS [*]
, RiskReferenceAS Td
, ''AS [*]
, InsuredAS Td
, ''AS [*]
, ClaimReferenceAS Td
, ''AS [*]
, UCRReferenceAS Td
, ''AS [*]
, FORMAT(IncurredRunningTotalGBP
-ReserveSettlementMovementGBP
-PaidSettlementMovementGBP,'#,##0.00;(#,##0.00)')AS Td
, ''AS [*]
, FORMAT(ReserveSettlementMovementGBP,'#,##0.00;(#,##0.00)')AS Td
, ''AS [*]
, FORMAT(PaidSettlementMovementGBP,'#,##0.00;(#,##0.00)')AS Td
, ''AS [*]
, FORMAT(IncurredRunningTotalGBP,'#,##0.00;(#,##0.00)')AS Td
FROM #RunningTotal
WHERE
(
(
IncurredRunningTotalGBP
-ReserveSettlementMovementGBP
-PaidSettlementMovementGBP
)>250000
OR
(
IncurredRunningTotalGBP>250000
AND (PaidSettlementMovement<>0
OR ReserveSettlementMovement<>0)
)
)
AND MovementDate>=@MinMovementDate AND MovementDate<@CurrentMovementDate
ORDER BY
MovementDate DESC,RiskReference,ClaimReference ASC
FOR XML PATH('tr')
)
/*
Add Header and Rows to a Table
*/
SET @Results= '<Table border = "1" "Solid" "Black"><font face = "calibri" size = 2>'+@TableHeader+@TableRows+'</font></Table>'
I'll see If I can dig out the article/post I got the inspiration from.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
March 23, 2015 at 5:53 am
I need to learn to read before posting.
It looks like you have an Un-escaped single quote somewhere in the code that creates the stylesheet, check the value of the variable @TBL_STYLE using a print to see whats been passed.
Also you can probably do away with all the Cursors, that you have to build the rows and columns using a bit more logic to build a column list and insert it into a string using STUFF, then build the row data from that, it might be a lot quicker.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
March 23, 2015 at 6:32 am
Most probably proc parameter @TBL_STYLE NVARCHAR(1024) is the problem.
When concatenating two NVARCHAR(n) strings, where n is not MAX, the length of the result can not exceed 4000. Intermediate result is first truncated before it's assigned to your output NVARCHAR(MAX) variable. Compare this assignments
declare @1 nvarchar(4000) = Replicate('1',3000);
declare @m1 nvarchar(MAX)= @1 + @1; ;
declare @m2 nvarchar(MAX) ='';
select @m2 = @m2 + @1 + @1;
select len(@m1), len(@m2);
March 23, 2015 at 11:03 pm
Hi All,
I tried changing the length of @tbl_style variable from 1024 to 2000. However i am getting the same error. The @tbl_style variable has to do nothing with the @output variable except it appends the table class name that was specified in the <style> tag.
The problem occurs only when i try to provide different styles such as for table and table columns. If I specify the style only for a table or a table columns i can get the output.
As a temporary fix, i have provided only one style for the table and its working fine.
It would be helpful if i can get a permanent fix....
March 24, 2015 at 12:17 am
The problem is that the @TABLE_STYLE variable is a fed to an inline style which means the style can't include style tags. E.g. "style = {Font-family: arial; color: red}"
A style tag should be inside the header. To do what you are trying to do change the @output line to:
SET @OUTPUT= ''<html><head><style type="text/css">table.gridtable { font-family: verdana,arial,sans-serif; font-size:10px; color:#333333;border-width:1px; border-color: #666666; border-collapse: collapse; } table.gridtable td {border-width: 1px; padding: 8px; border-style: solid; border-color: #666666; background-color: #ffffff;}</style>''+ @TBL_STYLE + ''</head><body><table>'' + @FIELDS + @HTMLROWS + ''</table></body></html>''
-- Itzik Ben-Gan 2001
March 24, 2015 at 1:07 am
karthik82.vk (3/23/2015)
Hi All,I tried changing the length of @tbl_style variable from 1024 to 2000. However i am getting the same error. The @tbl_style variable has to do nothing with the @output variable except it appends the table class name that was specified in the <style> tag.
The problem occurs only when i try to provide different styles such as for table and table columns. If I specify the style only for a table or a table columns i can get the output.
As a temporary fix, i have provided only one style for the table and its working fine.
It would be helpful if i can get a permanent fix....
Looks like another evidence that the result of concatenation is truncated when the lehgth of concatenated strings is too big.
To avoid truncation try
...
SET @OUTPUT=''''
IF @maxrows>0
SET @OUTPUT= @OUTPUT + ''<html><head><style type="text/css">table.gridtable { font-family: verdana,arial,sans-serif; font-size:10px; color:#333333;border-width:1px; border-color: #666666; border-collapse: collapse; } table.gridtable td {border-width: 1px; padding: 8px; border-style: solid; border-color: #666666; background-color: #ffffff;}</style></head><body><table ' + @TBL_STYLE + '>'' + @FIELDS + @HTMLROWS + ''</table></body></html>''
DEALLOCATE col
'
END
Viewing 6 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply