html - 将 SQL Server 2008 表动态转换为 HTML 表

Question

有没有一种方法可以将 SQL Server 2008 表转换为 HTML 表文本，而无需先了解表的结构？

我试过这个:

USE [Altiris]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spCustomTable2HTML] (
@TABLENAME  NVARCHAR(500),
@OUTPUT   NVARCHAR(MAX) OUTPUT,
@TBL_STYLE NVARCHAR(1024) = '',
@HDR_STYLE NVARCHAR(1024) = '')
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)


--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)

--Make a copy of the original table adding an indexing columnWe 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 + ' 

--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 

-- @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!
SET @FIELDS=''<tr ' + @HDR_STYLE + '>''
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>''

-- @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=0
SET @ParmDefinition=N''@ROWOUT NVARCHAR(MAX) OUTPUT,@rowNum_IN INT''

While @rowNum < @maxrows
BEGIN
  SET @HTMLROWS=@HTMLROWS + ''<tr>''

  SET @rowNum=@rowNum +1
  OPEN col
  FETCH NEXT FROM col INTO @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)''

      EXEC  sp_executesql 
            @exec_str,
            @ParmDefinition,
            @ROWOUT=@ROW OUTPUT,
            @rowNum_IN=@rownum

      SET @HTMLROWS =@HTMLROWS +  ''<td>'' + @ROW + ''</td>''
      FETCH NEXT FROM col INTO @ColumnName
    END
  CLOSE col
  SET @HTMLROWS=@HTMLROWS + ''</tr>''
END

SET @OUTPUT=''''
IF @maxrows>0
SET @OUTPUT= ''<table ' + @TBL_STYLE + '>'' + @FIELDS + @HTMLROWS + ''</table>''

DEALLOCATE col
'

DECLARE @ParamDefinition nvarchar(max)
SET @ParamDefinition=N'@OUTPUT NVARCHAR(MAX) OUTPUT'

--Execute Dynamic SQL. HTML table is stored in @OUTPUT which is passed back up (as it's
--a parameter to this SP)
EXEC sp_executesql @exec_str,
@ParamDefinition,
@OUTPUT=@OUTPUT OUTPUT

RETURN 1

但是当我执行程序时

DECLARE @HTML NVARCHAR(MAX)

EXEC SpCustomTable2HTML 'Users', @HTML OUTPUT

SELECT @HTML

它一直返回 null。

有什么想法吗？

Answer 1

这SQL Fiddle DEMO显示你的问题。当所有行中的所有列都有值时，您将获得一个正确的 HTML 表格。当存在任何 NULL 时，它会将整个事物变为 NULL，因为

NULL + <any> = NULL

要修复它，只需更改第 90 行以处理空值，即

SET @HTMLROWS =@HTMLROWS + '''' + ISNULL(@ROW,'''') + ''''

固定SQL Fiddle DEMO