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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy