A veces puede ser interesante montar una tabla HTML con un simple QUERY. Con el siguiente procedimiento almacenado podemos hacerlo:
Obtenido de: https://stackoverflow.com/questions/7070053/convert-a-sql-query-result-table-to-an-html-table-for-email
-- Description: Turns a query into a formatted HTML table. Useful for emails.
-- Any ORDER BY clause needs to be passed in the separate ORDER BY parameter.
-- =============================================
CREATE PROC [dbo].[spQueryToHtmlTable]
(
@query nvarchar(MAX), –A query to turn into HTML format. It should not include an ORDER BY clause.
@orderBy nvarchar(MAX) = NULL, –An optional ORDER BY clause. It should contain the words ‘ORDER BY’.
@html nvarchar(MAX) = NULL OUTPUT –The HTML output of the procedure.
)
AS
BEGIN
SET NOCOUNT ON;
IF @orderBy IS NULL BEGIN
SET @orderBy = »
END
SET @orderBy = REPLACE(@orderBy, »», »»»);
DECLARE @realQuery nvarchar(MAX) = ‘
DECLARE @headerRow nvarchar(MAX);
DECLARE @cols nvarchar(MAX);
SELECT * INTO #dynSql FROM (‘ + @query + ‘) sub;
SELECT @cols = COALESCE(@cols + », »»»», », »») + »[» + name + »] AS »»td»»»
FROM tempdb.sys.columns
WHERE object_id = object_id(»tempdb..#dynSql»)
ORDER BY column_id;
SET @cols = »SET @html = CAST(( SELECT » + @cols + » FROM #dynSql ‘ + @orderBy + ‘ FOR XML PATH(»»tr»»), ELEMENTS XSINIL) AS nvarchar(max))»
EXEC sys.sp_executesql @cols, N»@html nvarchar(MAX) OUTPUT», @html=@html OUTPUT
SELECT @headerRow = COALESCE(@headerRow + »», »») + »<th>» + name + »</th>»
FROM tempdb.sys.columns
WHERE object_id = object_id(»tempdb..#dynSql»)
ORDER BY column_id;
SET @headerRow = »<tr>» + @headerRow + »</tr>»;
SET @html = »<table border=»1″>» + @headerRow + @html + »</table>»;
‘;
EXEC sys.sp_executesql @realQuery, N’@html nvarchar(MAX) OUTPUT’, @html=@html OUTPUT
END
GO
Una vez generado el procedimiento almacenado su uso es muy sencillo:
DECLARE @html nvarchar(MAX);
EXEC spQueryToHtmlTable @html = @html OUTPUT, @query = N'SELECT CODART,DESCART FROM dbo.aRTICULO', @orderBy = N'ORDER BY CODART';
PRINT @html