How to Export Data from a SQL Server Table to HTML

Views: 8.031 views

In this article

Reading Time: 5 minutes

Dear,
Good afternoon.

In this post I will demonstrate how to export data from a SQL Server table to an HTML file or a variable of type VARCHAR, where two very similar Procedures will be presented, where one generates the HTML as a physical file on disk and the other writes the HTML generated in an OUTPUT variable.

I also made a post about this same functionality, but using the CLR, which allows exporting a query to HTML faster and more practical than using OLE Automation. If you want to know more, visit the post SQL Server - How to Email a Query Result in HTML Format Using CLR (C #).

Prerequisites

If you only need to use the procedure where HTML is returned as an OUTPUT variable, you can skip the prerequisites.

For the procedure where a physical file is generated on disk, I need you to have created the procedure stpWrite_File_Filewhich I talked about her in the post File Operations Using OLE Automation in SQL Server.

Remember that to use OLE Automation, this feature must be enabled on the server. If not enabled, see the post Enabling OLE Automation via T-SQL on SQL Server how to do this.

Creating a test data mass

IF (OBJECT_ID('tempdb..##Teste') IS NOT NULL) DROP TABLE ##Teste
SELECT *
INTO ##Teste
FROM master.INFORMATION_SCHEMA.TABLES

IF (OBJECT_ID('tempdb..##Teste') IS NOT NULL) DROP TABLE ##Teste

SELECT *

INTO ##Teste

FROM master.INFORMATION_SCHEMA.TABLES

Returning HTML as an OUTPUT variable

This functionality should be used when you need to handle or perform some action with the generated HTML, such as sending an email with the data in this table, for example.

Example of use:

DECLARE @HTML VARCHAR(MAX)

EXEC dbo.stpExporta_Tabela_HTML_Output
    @Ds_Tabela = '##Teste', -- varchar(max)
    @Ds_Saida = @HTML OUT -- varchar(max)

PRINT @HTML

DECLARE @HTML VARCHAR(MAX)

EXEC dbo.stpExporta_Tabela_HTML_Output

@Ds_Tabela = '##Teste', -- varchar(max)

@Ds_Saida = @HTML OUT -- varchar(max)

PRINT @HTML

Example usage with centered text and sorting results by TABLE_NAME column:

DECLARE @HTML VARCHAR(MAX)

EXEC dbo.stpExporta_Tabela_HTML_Output
    @Ds_Tabela = '##Teste', -- varchar(max)
    @Ds_Saida = @HTML OUT, -- varchar(max)
    @Ds_Alinhamento = 'center',
    @Ds_OrderBy = 'TABLE_NAME'

PRINT @HTML

DECLARE @HTML VARCHAR(MAX)

EXEC dbo.stpExporta_Tabela_HTML_Output

@Ds_Tabela = '##Teste', -- varchar(max)

@Ds_Saida = @HTML OUT, -- varchar(max)

@Ds_Alinhamento = 'center',

@Ds_OrderBy = 'TABLE_NAME'

PRINT @HTML

Source code
View source

CREATE PROCEDURE [dbo].[stpExporta_Tabela_HTML_Output]
    @Ds_Tabela [varchar](max),
    @Fl_Aplica_Estilo_Padrao BIT = 1,
	@Ds_Alinhamento VARCHAR(10) = 'left',
	@Ds_OrderBy VARCHAR(MAX) = '',
    @Ds_Saida VARCHAR(MAX) OUTPUT
AS
BEGIN
    
    
    SET NOCOUNT ON
    
    
    DECLARE
        @query NVARCHAR(MAX),
        @Database sysname,
        @Nome_Tabela sysname

    
    
    IF (LEFT(@Ds_Tabela, 1) = '#')
    BEGIN
        SET @Database = 'tempdb.'
        SET @Nome_Tabela = @Ds_Tabela
    END
    ELSE BEGIN
        SET @Database = LEFT(@Ds_Tabela, CHARINDEX('.', @Ds_Tabela))
        SET @Nome_Tabela = SUBSTRING(@Ds_Tabela, LEN(@Ds_Tabela) - CHARINDEX('.', REVERSE(@Ds_Tabela)) + 2, LEN(@Ds_Tabela))
    END

    
    SET @query = '
    SELECT ORDINAL_POSITION, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE
    FROM ' + @Database + 'INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME = ''' + @Nome_Tabela + '''
    ORDER BY ORDINAL_POSITION'
    
    
    IF (OBJECT_ID('tempdb..#Colunas') IS NOT NULL) DROP TABLE #Colunas
    CREATE TABLE #Colunas (
        ORDINAL_POSITION int, 
        COLUMN_NAME sysname, 
        DATA_TYPE nvarchar(128), 
        CHARACTER_MAXIMUM_LENGTH int,
        NUMERIC_PRECISION tinyint, 
        NUMERIC_SCALE int
    )

    INSERT INTO #Colunas
    EXEC(@query)

    
    
    IF (@Fl_Aplica_Estilo_Padrao = 1)
    BEGIN
    
    SET @Ds_Saida = '<html>
<head>
    <title>Titulo</title>
    <style type="text/css">
        table { padding:0; border-spacing: 0; border-collapse: collapse; }
        thead { background: #00B050; border: 1px solid #ddd; }
        th { padding: 10px; font-weight: bold; border: 1px solid #000; color: #fff; }
        tr { padding: 0; }
        td { padding: 5px; border: 1px solid #cacaca; margin:0; text-align:' + @Ds_Alinhamento + '; }
    </style>
</head>'
    
    END
    
    
    
    SET @Ds_Saida = ISNULL(@Ds_Saida, '') + '
<table>
    <thead>
        <tr>'


    -- Cabeçalho da tabela
    DECLARE 
        @contadorColuna INT = 1, 
        @totalColunas INT = (SELECT COUNT(*) FROM #Colunas), 
        @nomeColuna sysname,
        @tipoColuna sysname
    

    WHILE(@contadorColuna <= @totalColunas)
    BEGIN

        SELECT @nomeColuna = COLUMN_NAME
        FROM #Colunas
        WHERE ORDINAL_POSITION = @contadorColuna


        SET @Ds_Saida = ISNULL(@Ds_Saida, '') + '
            <th>' + @nomeColuna + '</th>'


        SET @contadorColuna = @contadorColuna + 1

    END



    SET @Ds_Saida = ISNULL(@Ds_Saida, '') + '
        </tr>
    </thead>
    <tbody>'


    
    -- Conteúdo da tabela

    DECLARE @saida VARCHAR(MAX)

    SET @query = '
SELECT @saida = (
    SELECT '


    SET @contadorColuna = 1

    WHILE(@contadorColuna <= @totalColunas)
    BEGIN

        SELECT 
            @nomeColuna = COLUMN_NAME,
            @tipoColuna = DATA_TYPE
        FROM 
            #Colunas
        WHERE 
            ORDINAL_POSITION = @contadorColuna



        IF (@tipoColuna IN ('int', 'bigint', 'float', 'numeric', 'decimal', 'bit', 'tinyint', 'smallint', 'integer'))
        BEGIN
        
            SET @query = @query + '
    ISNULL(CAST([' + @nomeColuna + '] AS VARCHAR(MAX)), '''') AS [td]'
    
        END
        ELSE BEGIN
        
            SET @query = @query + '
    ISNULL([' + @nomeColuna + '], '''') AS [td]'
    
        END
    
        
        IF (@contadorColuna < @totalColunas)
            SET @query = @query + ','

        
        SET @contadorColuna = @contadorColuna + 1

    END



    SET @query = @query + '
FROM ' + @Ds_Tabela + (CASE WHEN ISNULL(@Ds_OrderBy, '') = '' THEN '' ELSE ' 
ORDER BY ' END) + @Ds_OrderBy + '
FOR XML RAW(''tr''), Elements
)'
    
    
    EXEC tempdb.sys.sp_executesql
        @query,
        N'@saida NVARCHAR(MAX) OUTPUT',
        @saida OUTPUT


    -- Identação
    SET @saida = REPLACE(@saida, '<tr>', '
        <tr>')

    SET @saida = REPLACE(@saida, '<td>', '
            <td>')

    SET @saida = REPLACE(@saida, '</tr>', '
        </tr>')


    SET @Ds_Saida = ISNULL(@Ds_Saida, '') + @saida


    
    SET @Ds_Saida = ISNULL(@Ds_Saida, '') + '
    </tbody>
</table>'
    
            
END

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

CREATE PROCEDURE [dbo].[stpExporta_Tabela_HTML_Output]

@Ds_Tabela [varchar](max),

@Fl_Aplica_Estilo_Padrao BIT = 1,

@Ds_Alinhamento VARCHAR(10) = 'left',

@Ds_OrderBy VARCHAR(MAX) = '',

@Ds_Saida VARCHAR(MAX) OUTPUT

BEGIN

SET NOCOUNT ON

DECLARE

@query NVARCHAR(MAX),

@Database sysname,

@Nome_Tabela sysname

IF (LEFT(@Ds_Tabela, 1) = '#')

BEGIN

SET @Database = 'tempdb.'

SET @Nome_Tabela = @Ds_Tabela

END

ELSE BEGIN

SET @Database = LEFT(@Ds_Tabela, CHARINDEX('.', @Ds_Tabela))

SET @Nome_Tabela = SUBSTRING(@Ds_Tabela, LEN(@Ds_Tabela) - CHARINDEX('.', REVERSE(@Ds_Tabela)) + 2, LEN(@Ds_Tabela))

END

SET @query = '

SELECT ORDINAL_POSITION, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE

FROM ' + @Database + 'INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = ''' + @Nome_Tabela + '''

ORDER BY ORDINAL_POSITION'

IF (OBJECT_ID('tempdb..#Colunas') IS NOT NULL) DROP TABLE #Colunas

CREATE TABLE #Colunas (

ORDINAL_POSITION int,

COLUMN_NAME sysname,

DATA_TYPE nvarchar(128),

CHARACTER_MAXIMUM_LENGTH int,

NUMERIC_PRECISION tinyint,

NUMERIC_SCALE int

)

INSERT INTO #Colunas

EXEC(@query)

IF (@Fl_Aplica_Estilo_Padrao = 1)

BEGIN

SET @Ds_Saida = '<html>

<head>

<title>Titulo</title>

table { padding:0; border-spacing: 0; border-collapse: collapse; }

thead { background: #00B050; border: 1px solid #ddd; }

th { padding: 10px; font-weight: bold; border: 1px solid #000; color: #fff; }

tr { padding: 0; }

td { padding: 5px; border: 1px solid #cacaca; margin:0; text-align:' + @Ds_Alinhamento + '; }

</style>

</head>'

END

SET @Ds_Saida = ISNULL(@Ds_Saida, '') + '

<table>

<thead>

<tr>'

-- Cabeçalho da tabela

DECLARE

@contadorColuna INT = 1,

@totalColunas INT = (SELECT COUNT(*) FROM #Colunas),

@nomeColuna sysname,

@tipoColuna sysname

WHILE(@contadorColuna <= @totalColunas)

BEGIN

SELECT @nomeColuna = COLUMN_NAME

FROM #Colunas

WHERE ORDINAL_POSITION = @contadorColuna

SET @Ds_Saida = ISNULL(@Ds_Saida, '') + '

<th>' + @nomeColuna + '</th>'

SET @contadorColuna = @contadorColuna + 1

END

SET @Ds_Saida = ISNULL(@Ds_Saida, '') + '

</tr>

</thead>

<tbody>'

-- Conteúdo da tabela

DECLARE @saida VARCHAR(MAX)

SET @query = '

SELECT @saida = (

SELECT '

SET @contadorColuna = 1

WHILE(@contadorColuna <= @totalColunas)

BEGIN

SELECT

@nomeColuna = COLUMN_NAME,

@tipoColuna = DATA_TYPE

FROM

#Colunas

WHERE

ORDINAL_POSITION = @contadorColuna

IF (@tipoColuna IN ('int', 'bigint', 'float', 'numeric', 'decimal', 'bit', 'tinyint', 'smallint', 'integer'))

BEGIN

SET @query = @query + '

ISNULL(CAST([' + @nomeColuna + '] AS VARCHAR(MAX)), '''') AS [td]'

END

ELSE BEGIN

SET @query = @query + '

ISNULL([' + @nomeColuna + '], '''') AS [td]'

END

IF (@contadorColuna < @totalColunas)

SET @query = @query + ','

SET @contadorColuna = @contadorColuna + 1

END

SET @query = @query + '

FROM ' + @Ds_Tabela + (CASE WHEN ISNULL(@Ds_OrderBy, '') = '' THEN '' ELSE '

ORDER BY ' END) + @Ds_OrderBy + '

FOR XML RAW(''tr''), Elements

EXEC tempdb.sys.sp_executesql

@query,

N'@saida NVARCHAR(MAX) OUTPUT',

@saida OUTPUT

-- Identação

SET @saida = REPLACE(@saida, '<tr>', '

<tr>')

SET @saida = REPLACE(@saida, '<td>', '

<td>')

SET @saida = REPLACE(@saida, '</tr>', '

</tr>')

SET @Ds_Saida = ISNULL(@Ds_Saida, '') + @saida

SET @Ds_Saida = ISNULL(@Ds_Saida, '') + '

</tbody>

</table>'

END

Final result:

Exporting HTML to File

This functionality should be used when you need to generate a physical file on disk or network containing the contents of a table in HTML format.

Example of use:

EXEC dbo.stpExporta_Tabela_HTML 
    @Ds_Tabela = '##Teste', -- varchar(max)
    @Ds_Arquivo_Saida = 'C:\Teste.html' -- varchar(max)

EXEC dbo.stpExporta_Tabela_HTML

@Ds_Tabela = '##Teste', -- varchar(max)

@Ds_Arquivo_Saida = 'C:\Teste.html' -- varchar(max)

Source code
View source

CREATE PROCEDURE [dbo].[stpExporta_Tabela_HTML]
    @Ds_Tabela [varchar](max),
    @Fl_Aplica_Estilo_Padrao BIT = 1,
    @Ds_Arquivo_Saida VARCHAR(500)
WITH EXECUTE AS CALLER
AS
BEGIN
    
    
    SET NOCOUNT ON
    
    
    DECLARE
        @query NVARCHAR(MAX),
        @Database sysname,
        @Nome_Tabela sysname,
        @Ds_Saida VARCHAR(MAX)

    
    
    IF (LEFT(@Ds_Tabela, 1) = '#')
    BEGIN
        SET @Database = 'tempdb.'
        SET @Nome_Tabela = @Ds_Tabela
    END
    ELSE BEGIN
        SET @Database = LEFT(@Ds_Tabela, CHARINDEX('.', @Ds_Tabela))
        SET @Nome_Tabela = SUBSTRING(@Ds_Tabela, LEN(@Ds_Tabela) - CHARINDEX('.', REVERSE(@Ds_Tabela)) + 2, LEN(@Ds_Tabela))
    END

    
    SET @query = '
    SELECT ORDINAL_POSITION, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE
    FROM ' + @Database + 'INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME = ''' + @Nome_Tabela + '''
    ORDER BY ORDINAL_POSITION'
    
    
    IF (OBJECT_ID('tempdb..#Colunas') IS NOT NULL) DROP TABLE #Colunas
    CREATE TABLE #Colunas (
        ORDINAL_POSITION int, 
        COLUMN_NAME sysname, 
        DATA_TYPE nvarchar(128), 
        CHARACTER_MAXIMUM_LENGTH int,
        NUMERIC_PRECISION tinyint, 
        NUMERIC_SCALE int
    )

    INSERT INTO #Colunas
    EXEC(@query)

    
    
    IF (@Fl_Aplica_Estilo_Padrao = 1)
    BEGIN
    
    SET @Ds_Saida = '<html>
<head>
    <title>Titulo</title>
    <style type="text/css">
        table { padding:0; border-spacing: 0; border-collapse: collapse; }
        thead { background: #00B050; border: 1px solid #ddd; }
        th { padding: 10px; font-weight: bold; border: 1px solid #000; color: #fff; }
        tr { padding: 0; }
        td { padding: 5px; border: 1px solid #cacaca; margin:0; }
    </style>
</head>'
    
    END
    
    
    
    SET @Ds_Saida = ISNULL(@Ds_Saida, '') + '
<table>
    <thead>
        <tr>'


    -- Cabeçalho da tabela
    DECLARE 
        @contadorColuna INT = 1, 
        @totalColunas INT = (SELECT COUNT(*) FROM #Colunas), 
        @nomeColuna sysname,
        @tipoColuna sysname
    

    WHILE(@contadorColuna <= @totalColunas)
    BEGIN

        SELECT @nomeColuna = COLUMN_NAME
        FROM #Colunas
        WHERE ORDINAL_POSITION = @contadorColuna


        SET @Ds_Saida = ISNULL(@Ds_Saida, '') + '
            <th>' + @nomeColuna + '</th>'


        SET @contadorColuna = @contadorColuna + 1

    END



    SET @Ds_Saida = ISNULL(@Ds_Saida, '') + '
        </tr>
    </thead>
    <tbody>'


    
    -- Conteúdo da tabela

    DECLARE @saida VARCHAR(MAX)

    SET @query = '
SELECT @saida = (
    SELECT '


    SET @contadorColuna = 1

    WHILE(@contadorColuna <= @totalColunas)
    BEGIN

        SELECT 
            @nomeColuna = COLUMN_NAME,
            @tipoColuna = DATA_TYPE
        FROM 
            #Colunas
        WHERE 
            ORDINAL_POSITION = @contadorColuna



        IF (@tipoColuna IN ('int', 'bigint', 'float', 'numeric', 'decimal', 'bit', 'tinyint', 'smallint', 'integer'))
        BEGIN
        
            SET @query = @query + '
    ISNULL(CAST(' + @nomeColuna + ' AS VARCHAR(MAX)), '''') AS [td]'
    
        END
        ELSE BEGIN
        
            SET @query = @query + '
    ISNULL(' + @nomeColuna + ', '''') AS [td]'
    
        END
    
        
        IF (@contadorColuna < @totalColunas)
            SET @query = @query + ','

        
        SET @contadorColuna = @contadorColuna + 1

    END



    SET @query = @query + '
FROM ' + @Ds_Tabela + '
FOR XML RAW(''tr''), Elements
)'
    
    
    EXEC tempdb.sys.sp_executesql
        @query,
        N'@saida NVARCHAR(MAX) OUTPUT',
        @saida OUTPUT


    -- Identação
    SET @saida = REPLACE(@saida, '<tr>', '
        <tr>')

    SET @saida = REPLACE(@saida, '<td>', '
            <td>')

    SET @saida = REPLACE(@saida, '</tr>', '
        </tr>')


    SET @Ds_Saida = ISNULL(@Ds_Saida, '') + @saida


    
    SET @Ds_Saida = ISNULL(@Ds_Saida, '') + '
    </tbody>
</table>'

    
    IF (@Ds_Saida IS NOT NULL)
    BEGIN
        
        EXEC dbo.stpEscreve_Arquivo_FSO
            @String = @Ds_Saida, -- varchar(max)
            @Ds_Arquivo = @Ds_Arquivo_Saida -- varchar(1501)
            
            
        PRINT 'Arquivo com o conteúdo da tabela "' + @Ds_Tabela + '" foi gerado no caminho "' + @Ds_Arquivo_Saida + '"'
            
            
    END
    ELSE
        PRINT 'O retorno dos dados está vazio.'
            
END

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

CREATE PROCEDURE [dbo].[stpExporta_Tabela_HTML]

@Ds_Tabela [varchar](max),

@Fl_Aplica_Estilo_Padrao BIT = 1,

@Ds_Arquivo_Saida VARCHAR(500)

WITH EXECUTE AS CALLER

BEGIN

SET NOCOUNT ON

DECLARE

@query NVARCHAR(MAX),

@Database sysname,

@Nome_Tabela sysname,

@Ds_Saida VARCHAR(MAX)

IF (LEFT(@Ds_Tabela, 1) = '#')

BEGIN

SET @Database = 'tempdb.'

SET @Nome_Tabela = @Ds_Tabela

END

ELSE BEGIN

SET @Database = LEFT(@Ds_Tabela, CHARINDEX('.', @Ds_Tabela))

SET @Nome_Tabela = SUBSTRING(@Ds_Tabela, LEN(@Ds_Tabela) - CHARINDEX('.', REVERSE(@Ds_Tabela)) + 2, LEN(@Ds_Tabela))

END

SET @query = '

SELECT ORDINAL_POSITION, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE

FROM ' + @Database + 'INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = ''' + @Nome_Tabela + '''

ORDER BY ORDINAL_POSITION'

IF (OBJECT_ID('tempdb..#Colunas') IS NOT NULL) DROP TABLE #Colunas

CREATE TABLE #Colunas (

ORDINAL_POSITION int,

COLUMN_NAME sysname,

DATA_TYPE nvarchar(128),

CHARACTER_MAXIMUM_LENGTH int,

NUMERIC_PRECISION tinyint,

NUMERIC_SCALE int

)

INSERT INTO #Colunas

EXEC(@query)

IF (@Fl_Aplica_Estilo_Padrao = 1)

BEGIN

SET @Ds_Saida = '<html>

<head>

<title>Titulo</title>

table { padding:0; border-spacing: 0; border-collapse: collapse; }

thead { background: #00B050; border: 1px solid #ddd; }

th { padding: 10px; font-weight: bold; border: 1px solid #000; color: #fff; }

tr { padding: 0; }

td { padding: 5px; border: 1px solid #cacaca; margin:0; }

</style>

</head>'

END

SET @Ds_Saida = ISNULL(@Ds_Saida, '') + '

<table>

<thead>

<tr>'

-- Cabeçalho da tabela

DECLARE

@contadorColuna INT = 1,

@totalColunas INT = (SELECT COUNT(*) FROM #Colunas),

@nomeColuna sysname,

@tipoColuna sysname

WHILE(@contadorColuna <= @totalColunas)

BEGIN

SELECT @nomeColuna = COLUMN_NAME

FROM #Colunas

WHERE ORDINAL_POSITION = @contadorColuna

SET @Ds_Saida = ISNULL(@Ds_Saida, '') + '

<th>' + @nomeColuna + '</th>'

SET @contadorColuna = @contadorColuna + 1

END

SET @Ds_Saida = ISNULL(@Ds_Saida, '') + '

</tr>

</thead>

<tbody>'

-- Conteúdo da tabela

DECLARE @saida VARCHAR(MAX)

SET @query = '

SELECT @saida = (

SELECT '

SET @contadorColuna = 1

WHILE(@contadorColuna <= @totalColunas)

BEGIN

SELECT

@nomeColuna = COLUMN_NAME,

@tipoColuna = DATA_TYPE

FROM

#Colunas

WHERE

ORDINAL_POSITION = @contadorColuna

IF (@tipoColuna IN ('int', 'bigint', 'float', 'numeric', 'decimal', 'bit', 'tinyint', 'smallint', 'integer'))

BEGIN

SET @query = @query + '

ISNULL(CAST(' + @nomeColuna + ' AS VARCHAR(MAX)), '''') AS [td]'

END

ELSE BEGIN

SET @query = @query + '

ISNULL(' + @nomeColuna + ', '''') AS [td]'

END

IF (@contadorColuna < @totalColunas)

SET @query = @query + ','

SET @contadorColuna = @contadorColuna + 1

END

SET @query = @query + '

FROM ' + @Ds_Tabela + '

FOR XML RAW(''tr''), Elements

EXEC tempdb.sys.sp_executesql

@query,

N'@saida NVARCHAR(MAX) OUTPUT',

@saida OUTPUT

-- Identação

SET @saida = REPLACE(@saida, '<tr>', '

<tr>')

SET @saida = REPLACE(@saida, '<td>', '

<td>')

SET @saida = REPLACE(@saida, '</tr>', '

</tr>')

SET @Ds_Saida = ISNULL(@Ds_Saida, '') + @saida

SET @Ds_Saida = ISNULL(@Ds_Saida, '') + '

</tbody>

</table>'

IF (@Ds_Saida IS NOT NULL)

BEGIN

EXEC dbo.stpEscreve_Arquivo_FSO

@String = @Ds_Saida, -- varchar(max)

@Ds_Arquivo = @Ds_Arquivo_Saida -- varchar(1501)

PRINT 'Arquivo com o conteúdo da tabela "' + @Ds_Tabela + '" foi gerado no caminho "' + @Ds_Arquivo_Saida + '"'

END

ELSE

PRINT 'O retorno dos dados está vazio.'

END

Final result:

That's it, guys.
To the next!

Jorge junior said:

10 November 2017 to 13: 02

I already found it in another post

- Dirceu Resende said:
  
  11 November 2017 to 01: 38
  
  Oops, wonderful!
  
Jorge junior said:

10 November 2017 to 12: 58

Hello Dirceu, I couldn't find the procedure code 'dbo.stpEscreve_Arquivo_FSO' that appears

- Dirceu Resende said:
  
  11 November 2017 to 01: 38
  
  Hi Jorge,
  All right?
  
  First, thanks for stopping by! The code of this SP is in the post https://www.dirceuresende.com/blog/operacoes-com-arquivos-utilizando-ole-automation-no-sql-server/, as I explained there in the “Prerequisites” part, right at the beginning of the post 🙂
  
  If you have any questions, do not hesitate to ask!
  Hug!
  
Queiroz Thiago said:

30 from 2016 to 10 at 34: XNUMX

Dirceu, good morning. Come accompanying your blog, congratulations excellent tips.

Ask me a question, I'm setting up a query to be sent via email, I already have everything configured, I can have more than one table in sending the email?

After the N ”I added a + to add another table:
N ”+

In Detailed Billings: DUTRA '+
N ”+

I am asking you this as the email is being received blank.

Thank you.

- Queiroz Thiago said:
  
  30 from 2016 to 11 at 34: XNUMX
  
  I managed to solve lol.
  I added several tables, and at the time of print put as isnull too.
  
  - Dirceu Resende said:
    
    31 from 2016 to 08 at 15: XNUMX
    
    That's it, Thiago! You can also do this by creating 2 variables: One will get the EXEC result for each table and sum the result in the other variable at the end, using ISNULL ().

Other Languages

Subscribe to a blog by email

Blog Views

Power Alerts

Azure Banking Admin Course

SQL Server Security Course

My Certifications

Posts Archive

Categories

Recent Posts

How to export data from a SQL Server table to HTML

Prerequisites

Creating a test data mass

Returning HTML as an OUTPUT variable

Exporting HTML to File

You may also like ...

Comments

Leave a Comment Cancel reply

Prerequisites

Creating a test data mass

Returning HTML as an OUTPUT variable

Exporting HTML to File

You may also like ...

TOP 10 2019 technical articles you liked best

SQL Server - How to integrate database with Slack and send messages using Python and sp_execute_external_script

[Video] - Introduction to SQLCLR

Comments

Leave a Comment Cancel reply