March 23, 2015 at 2:45 am
Hi,
I am working on a code that will convert the query output into a html query output. That is the output of the query will be along with html tags so that we can save it as a html file.
The stored procedure that i have used is downloaded from symantec website and is working fine. Below is the code of that stored procedure.
/****** Object: StoredProcedure [dbo].[sp_Table2HTML] Script Date: 12/21/2011 09:04:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Table2HTML] (
@TABLENAME NVARCHAR(500),
@OUTPUT NVARCHAR(MAX) OUTPUT,
@TBL_STYLE NVARCHAR(1024) = '',
@ALIGNMENT INT =0 )
AS
-- @exec_str stores the dynamic SQL Query
-- @ParmDefinition stores the parameter definition for the dynamic SQL
DECLARE @exec_str NVARCHAR(MAX)
DECLARE @ParmDefinition NVARCHAR(500)
IF @ALIGNMENT=0
BEGIN
--We need to use Dynamic SQL at this point so we can expand the input table name parameter
SET @exec_str= N'
DECLARE @exec_str NVARCHAR(MAX)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @DEBUG INT
SET @DEBUG=0
IF @DEBUG=1 Print ''Table2HTML -Horizontal alignment''
--Make a copy of the original table adding an indexing column. We need to add an index column to the table to facilitate sorting so we can maintain the
--original table order as we iterate through adding HTML tags to the table fields.
--New column called CustColHTML_ID (unlikely to be used by someone else!)
--
select CustColHTML_ID=0,* INTO #CustomTable2HTML FROM ' + @TABLENAME + '
IF @DEBUG=1 PRINT ''Created temporary custom table''
--Now alter the table to add the auto-incrementing index. This will facilitate row finding
DECLARE @COUNTER INT
SET @COUNTER=0
UPDATE #CustomTable2HTML SET @COUNTER = CustColHTML_ID=@COUNTER+1
IF @DEBUG=1 PRINT ''Added counter column to custom table''
-- @HTMLROWS will store all the rows in HTML format
-- @ROW will store each HTML row as fields on each row are iterated through
-- using dymamic SQL and a cursor
-- @FIELDS will store the header row for the HTML Table
DECLARE @HTMLROWS NVARCHAR(MAX) DECLARE @FIELDS NVARCHAR(MAX)
SET @HTMLROWS='''' DECLARE @ROW NVARCHAR(MAX)
-- Create the first HTML row for the table (the table header). Ignore our indexing column!
SELECT @FIELDS=COALESCE(@FIELDS, '' '','''')+''<td>'' + name + ''</td>''
FROM tempdb.sys.Columns
WHERE object_id=object_id(''tempdb..#CustomTable2HTML'')
AND name not like ''CustColHTML_ID''
SET @FIELDS=@FIELDS + ''</tr>''
IF @DEBUG=1 PRINT ''table fields: '' + @FIELDS
-- @ColumnName stores the column name as found by the table cursor
-- @maxrows is a count of the rows in the table, and @rownum is for marking the
-- ''current'' row whilst processing
DECLARE @ColumnName NVARCHAR(500)
DECLARE @maxrows INT
DECLARE @rownum INT
--Find row count of our temporary table
SELECT @maxrows=count(*) FROM #CustomTable2HTML
--Create a cursor which will look through all the column names specified in the temporary table
--but exclude the index column we added (CustColHTML_ID)
DECLARE col CURSOR FOR
SELECT name FROM tempdb.sys.Columns
WHERE object_id=object_id(''tempdb..#CustomTable2HTML'')
AND name not like ''CustColHTML_ID''
ORDER BY column_id ASC
--For each row, generate dymanic SQL which requests the each column name in turn by
--iterating through a cursor
SET @rowNum=1
SET @ParmDefinition=N''@ROWOUT NVARCHAR(MAX) OUTPUT,@rowNum_IN INT''
While @rowNum <= @maxrows
BEGIN
SET @HTMLROWS=@HTMLROWS + ''<tr>''
OPEN col
FETCH NEXT FROM col INTO @ColumnName
IF @DEBUG=1 Print ''@ColumnName: '' + @ColumnName
WHILE @@FETCH_STATUS=0
BEGIN
--Get nth row from table
--SET @exec_str=''SELECT @ROWOUT=(select top 1 ['' + @ColumnName + ''] from (select top '' + cast(@rownum as varchar) + '' * from #CustomTable2HTML order by CustColHTML_ID ASC) xxx order by CustColHTML_ID DESC)''
SET @exec_str=''SELECT @ROWOUT=(select ['' + @ColumnName + ''] from #CustomTable2HTML where CustColHTML_ID=@rowNum_IN)''
IF @DEBUG=1 PRINT ''@exec_str: '' + @exec_str
EXEC sp_executesql
@exec_str,
@ParmDefinition,
@ROWOUT=@ROW OUTPUT,
@rowNum_IN=@rownum
IF @DEBUG=1 SELECT @ROW as ''@Row''
SET @HTMLROWS =@HTMLROWS + ''<td>'' + IsNull(@ROW,'''') + ''</td>''
FETCH NEXT FROM col INTO @ColumnName
END
CLOSE col
SET @rowNum=@rowNum +1
SET @HTMLROWS=@HTMLROWS + ''</tr>''
END
SET @OUTPUT=''''
IF @maxrows>0
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></head><body><table ' + @TBL_STYLE + '>'' + @FIELDS + @HTMLROWS + ''</table></body></html>''
DEALLOCATE col
'
END
The code works fine and i am able to get the output with html tags. The problem occurs when i insert stylesheet in the code. I tried to enforce styles using a stylesheet for the table returned in my sql code, so that it will look good. below is the stylesheet code that i inserted between <head> and </head> tags.
<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>
If I run the procedure without the style sheet code, it works fine. but when i run the procedure with style sheet code i am getting the below errors.
Msg 105, Level 15, State 1, Line 98
Unclosed quotation mark after the character string '</table></body><'.
Msg 102, Level 15, State 1, Line 98
Incorrect syntax near '</table></body><'.
Msg 105, Level 15, State 1, Line 98
Unclosed quotation mark after the character string '</table></body></'.
Msg 102, Level 15, State 1, Line 98
Incorrect syntax near '</table></body></'.
Msg 105, Level 15, State 1, Line 98
Unclosed quotation mark after the character string '</table></body><'.
Msg 102, Level 15, State 1, Line 98
Incorrect syntax near '</table></body><'.
Msg 105, Level 15, State 1, Line 98
Unclosed quotation mark after the character string '</table></bo'.
Msg 102, Level 15, State 1, Line 98
Incorrect syntax near '</table></bo'.
Msg 105, Level 15, State 1, Line 98
Unclosed quotation mark after the character string '</table></bod'.
Msg 102, Level 15, State 1, Line 98
Incorrect syntax near '</table></bod'.
I checked the code and i am not able to find what is the mistake. I tried changing the quotation mark but it didnt worked.
Can anyone help me to solve this?
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 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply