Part-2 SQL SERVER - 2008 - Configure Database Mail - Send Email From SQL Database Sending Mail in HTML Format

Part-2 SQL SERVER - 2008 - Configure Database Mail - Send Email From SQL Database Sending Mail in HTML Format

I am just providing the steps that are enough to give you the basic undrstanding to create HTML Format


-- Creating HTML Format Using Stored Procedures for sending email from database the following Stored Procedures Required


-- 1) Create DataSourceCheck stored procedures

CREATE PROCEDURE [dbo].[DataSourceCheck]

@dataSource varchar (100) = NULL,

@db varchar(50) = NULL output,

@table varchar(100) = NULL output

AS

BEGIN

DECLARE

@buffer varchar(100),

@object varchar(100),

@objectId bigint,

@schema varchar(50),

@rcd_cnt int,

@tableHtml varchar(200),

@sql nvarchar(1000)

SET @buffer = @dataSource;

--cannot accesss a local temp table. Return.

IF SUBSTRING (@buffer, 1, 1) = '#' and SUBSTRING (@buffer, 2, 1) <> '#'

BEGIN

--use LEFT 25 to make sure the local temp table name isn't too long for the @table varchar(100) variable.

SET @table = '
Table ' + LEFT (@dataSource, 25) + ' is a local temp table. Must use a global temp or permanent table.
';

RETURN;

END;

--set up the object name in the right format so you can check the OBJECT_ID

ELSE IF (SUBSTRING (@buffer, 1, 2) = '##')

BEGIN

SET @db = 'tempdb';

SET @table = @dataSource;

SET @object = @db + '..' + @table; --need to include tempdb so OBJECT_ID finds the temp table

END;

ELSE

BEGIN

--deal with schema

SET @db = SUBSTRING (@buffer, 1, charindex ('.', @buffer) - 1);

SET @buffer = replace (@buffer, @db + '.', '');

IF SUBSTRING (@buffer, 1, 1) = '.'

BEGIN

SET @schema = '..';

SET @buffer = replace (@buffer, '.', '');

END

ELSE

BEGIN

SET @schema = SUBSTRING (@buffer, 1, charindex ('.', (@buffer)) - 1);

SET @buffer = replace (@buffer, @schema + '.', '');

END

SET @table = @buffer;

SET @object = @dataSource;

END;

--does our data source exist? Check the object_id. If object does not exist, return.

SET @objectId = OBJECT_ID (@object, 'U');

IF @objectId is NULL

BEGIN

SET @db = NULL;

SET @table = '
Table ' + @dataSource + ' does not exist or is improperly qualified.
';

RETURN;

END;

--we have a valid data source. Check that it has rows and notify if empty.

SET @sql = 'SELECT @rcd_cnt = count(*) from ' + @dataSource;

EXEC master.sys.sp_executesql @sql, N'@rcd_cnt int OUTPUT', @rcd_cnt OUTPUT;

IF @rcd_cnt = 0

BEGIN

SET @db = NULL;

SET @table = '
Table ' + @dataSource + ' is empty.
';

RETURN;

END;

END


-- 2) Create Stored Procedures For HtmlTable


CREATE PROCEDURE [dbo].[HtmlTable]

@data_source varchar (100) = NULL,

@tableHTML varchar(max) OUTPUT

AS

BEGIN

SET NOCOUNT ON;

DECLARE

@db varchar(50),

@table varchar(100),

@cmd varchar(400),

@rcd_cnt int,

@sql nvarchar(1000);

--use procedure DataSourceCheck to see if @data_source is valid

EXEC dbo.DataSourceCheck @data_source, @db output, @table output;

IF @db is NULL --if the data source is not good, @db comes back NULL, and @table holds info as to the problem (either the table does not exist, or it is empty).

BEGIN

SET @tableHtml = @table;

RETURN;

END;


--We have a good table. Use information_schema metadata for table to get column names.

IF OBJECT_ID ('tempdb..##columnNames') IS not null DROP TABLE ##columnNames;

CREATE table ##columnNames (column_name varchar(50), position int identity);


SET @sql = 'USE ' + @db + '; INSERT into ##columnNames SELECT column_name from information_schema.columns where table_name = ''' + @table + ''' order by ordinal_position';

EXEC master.sys.sp_executesql @sql;


--use ##columnNames to create table ##columnPivot with the proper number of fields to hold data

IF OBJECT_ID ('tempdb..##columnPivot') IS not null DROP TABLE ##columnPivot;

CREATE table ##columnPivot (f1 varchar(200));


DECLARE

@i int = 2,

@fieldct int,

@column varchar(50),

@field varchar(200),

@value varchar(100),

@html varchar(max) = '';

SET @fieldct = (SELECT COUNT(*) from ##columnNames);

WHILE @i <= @fieldct --loop through adding a field to ##columnPivot for each column. Max field len is 200.

BEGIN

SET @sql = 'ALTER table ##columnPivot ADD f' + cast (@i as varchar(2)) + ' varchar(200)';

EXEC master.sys.sp_executesql @sql;

SET @i = @i + 1;

END

--##columnPivot is constructed but empty. Columns are named f1, f2, f3, etc

--construct dynamic SQL string that will be executed to populate ##columnPivot

SET @sql = 'INSERT into ##columnPivot SELECT ';

SET @i = 1;

SET @fieldct = (SELECT count(*) from ##columnNames);


WHILE @i <= @fieldct - 1

BEGIN

SET @column = (SELECT top 1 column_name from ##columnNames where position = cast (@i as varchar(2)));

SET @field = 'CAST([' + @column + '] as varchar(200)),';

SET @sql = @sql + @field;

SET @i = @i + 1;

END


SET @column = (SELECT top 1 column_name from ##columnNames where position = @fieldct);

SET @field = 'CAST([' + @column + '] as varchar(200)) FROM ' + @data_source;

SET @sql = @sql + @field; --@sql now contains the SQL statement that will insert data from @data_source into ##columnPivot

--execute @sql to insert into ##columnPivot the data from @data_source table

EXEC master.sys.sp_executesql @sql;


--format the output

IF OBJECT_ID ('tempdb..#columns') IS not null DROP TABLE #columns;

--use a copy of ##columnNames, because next steps delete from this table, and ##columnNames data is needed below. Does not need to be a global temp.

SELECT *

into #columns

from ##columnNames

order by position;

SET @fieldct = (SELECT count(*) from #columns);

SET @i = 1;

--create the header row for the table containing column names from the @data_source

WHILE @i <= @fieldct

BEGIN

SET @field = (SELECT top 1 column_name from #columns order by position);

SET @html = @html + '
' + @field + '
';

SET @i = @i + 1;

DELETE from #columns where column_name = @field;

END


SET @html = ' ' + @html + ' '; --now @html contains the header row of the output table


--populate ##columnPivot by working through the data row by row.

ALTER table ##columnPivot add id_key int identity;

DECLARE

@j int = 1,

@fieldcnt int,

@cell varchar(100),

@row varchar(500) = '';

SET @i = 1;

SET @fieldcnt = (SELECT count(*) from ##columnNames);

SET @rcd_cnt = (SELECT count(*) from ##columnPivot);

WHILE @i <= @rcd_cnt --this loop executes one time for each row of data

BEGIN

SET @j = 1;

WHILE @j <= @fieldcnt --this loop executes one time for each column (cell) of data

BEGIN

SET @sql = 'SELECT @value = f' + cast (@j as varchar(2)) + ' from ##columnPivot where id_key = ' + cast (@i as varchar(2));

EXEC master.sys.sp_executesql @sql, N'@value varchar(200) OUTPUT', @value OUTPUT;

SET @cell = '
' + ISNULL (@value, '
') + '
'; --need to use
if the cell is empty

SET @row = @row + @cell;

SET @j = @j + 1;

END

SET @row = ' ' + @row + ' ';

SET @html = @html + @row;

SET @row = '';

DELETE from ##columnPivot where id_key = cast (@i as varchar(2));

SET @i = @i + 1;

END

SET @tableHTML = '
' + @html + '
';

END


_________________________________________________________________________________

Testing With the below given query temp query


CREATE table ##Temptest (test1 int, test2 varchar(20), test3 datetime)

INSERT into ##Temptest values (1, 'Testing1', getdate())

INSERT into ##Temptest values (2, 'Testing2', '05/05/15')

DECLARE @tableHtml varchar(max)

EXEC dbo.HtmlTable

'##foo',

@tableHtml output

PRINT @tableHtml


-- Forcefully want to delete the global temporary table use this

drop table ##Temptest