Pivoting Data Using SQL 2005 (Part 3)

If you have been following this series, I have been focusing on many different aspects of dynamic pivoting using SQL 2005. In the first part of this series,http://jahaines.blogspot.com/2009/08/pivoting-data-using-sql-2005-part-1.html, I focused on creating a static pivot query. In the second part of this series, http://jahaines.blogspot.com/2009/08/pivoting-data-using-sql-2005-part-2.html,I focused on creating a dynamic pivot, where the pivot values were unknown. We are now going to ramp up this series with a completely dynamic pivot. In this series, will will pivot data where the columns are stored as rows in a table and the values are not known.

In this example, we will be looking at employee sales. Let’s start by creating and populating our sample tables.

SET NOCOUNT ON

GO

IF OBJECT_ID('tempdb..#Pivot_Columns') IS NOT NULL

BEGIN

    DROP TABLE #Pivot_Columns;

END

GO

CREATE TABLE #Pivot_Columns(

ColId INT PRIMARY KEY,

TableName VARCHAR(25),

ColName VARCHAR(25)

);

GO

INSERT INTO #Pivot_Columns VALUES (1,'#Sales','YearMonth');

INSERT INTO #Pivot_Columns VALUES (2,'#Sales','SalesAmt');

IF OBJECT_ID('tempdb..#EmpSales') IS NOT NULL

BEGIN

    DROP TABLE #EmpSales;

END

GO

CREATE TABLE #EmpSales(

EmpSalesId INT IDENTITY(1,1) PRIMARY KEY,

EmpId INT,

SalesId INT,

ColId INT,

ColTxt VARCHAR(50)

);

GO

INSERT INTO #EmpSales VALUES (1,1,1,'2008-08');

INSERT INTO #EmpSales VALUES (1,1,2,'10000');

INSERT INTO #EmpSales VALUES (2,3,1,'2008-07');

INSERT INTO #EmpSales VALUES (2,3,2,'8500.25');

INSERT INTO #EmpSales VALUES (3,4,1,'2008-08');

INSERT INTO #EmpSales VALUES (3,4,2,'5999.99');

Now we have our table, let’s have a look at an example.

DECLARE @sql NVARCHAR(MAX),

        @Pivot_Cols VARCHAR(MAX)

--The code below reads through all the data in the table and

--builds a distinct column list.

SELECT 

    @Pivot_Cols = COALESCE(@Pivot_Cols + ',','') + QUOTENAME(ColName)

FROM #Pivot_Columns

GROUP BY QUOTENAME([ColName])

ORDER BY QUOTENAME(ColName) ASC

SELECT @sql = N'

SELECT 

    EmpId,' + @Pivot_Cols + '

FROM(

    SELECT e.EmpId,    e.ColTxt,pc.ColName

    FROM #EmpSales e

    INNER JOIN #Pivot_Columns pc ON e.ColId = pc.ColId

) AS dataToPivotTable

PIVOT(

    MAX(ColTxt) --Aggregate Function

    FOR [ColName]-- Column To Pivot

    IN (' + @Pivot_Cols + ') --Values to Pivot

) AS whatToPivotOnTable;'

--PRINT @sql

EXEC sp_executesql @sql

GO

Results:

As you can see from the screenshot above, we have successfully pivoting the rows of data into columns. Is this all that there is to dynamic pivoting?…..not quite. If you look closely at the example above you will notice that we are using the MAX aggregate to get the maximum of each column. This is fine if you expect a single set of rows, but what do you think will happen when an employee has more than one set of rows? The short answer is the data would be rendered invalid. The data may become invalid because the maximum YearMonth value, may be from a different month than the maximum SalesAmt value. To demonstrate this behavior, we will add a new set of rows into our table.

INSERT INTO #EmpSales VALUES (1,2,1,'2008-09');

INSERT INTO #EmpSales VALUES (1,2,2,'1');

Now execute the same query and your results should look like the screenshot below. The sales amount column has a maximum value of 10,000 and the YearMonth is 2008-09. If you look carefully at the data we inserted, the SalesAmt value should be 1, for 2008-09, not 10,000.

So how do we resolve this issue? Let’s press on. We are going to modify our query a bit.

DECLARE @sql NVARCHAR(MAX),

        @Pivot_Cols VARCHAR(MAX)

--The code below reads through all the data in the table and

--builds a distinct column list.

SELECT 

    @Pivot_Cols = COALESCE(@Pivot_Cols + ',','') + QUOTENAME(e.ColTxt)

FROM #EmpSales e

INNER JOIN [#Pivot_Columns] pc

    ON e.[ColId] = pc.[ColId]

WHERE pc.[ColName] = 'YearMonth'

GROUP BY QUOTENAME(e.ColTxt)

ORDER BY QUOTENAME(e.ColTxt)

SELECT @sql = N'

SELECT 

    EmpId,' + @Pivot_Cols + '

FROM(

    SELECT 

        e.EmpId,

        e.ColTxt as MonthYear,

        CONVERT(NUMERIC(9,2),e2.[ColTxt]) AS [ColTxt]

     FROM #EmpSales e INNER JOIN #EmpSales e2 ON e.EmpId = e2.EmpId AND e.SalesId = e2.SalesId 

     INNER JOIN #Pivot_Columns pc ON pc.ColID = e.[ColId]

     INNER JOIN #Pivot_Columns pc2 ON pc2.ColId = e2.[ColId]

     WHERE pc.ColName = ''YearMonth'' AND pc2.ColName = ''SalesAmt''

) AS dataToPivotTable

PIVOT(

    SUM([ColTxt]) --Aggregate Function

    FOR [MonthYear]-- Column To Pivot

    IN (' + @Pivot_Cols + ') --Values to Pivot

) AS whatToPivotOnTable;'

PRINT @sql

EXEC sp_executesql @sql

GO

The code is very similar in most respects, but the biggest difference is I am not pivoting on ColName. Instead, I am going back to the methods we learned in part 2 of this series. The key to dynamically pivoting data is to know your data. Depending on what you are trying to pivot, you may need to aggregate data or simply use the MAX pivot. In this case, we need to use a more powerful pivot statement. In the variable assignment portion of the modifed code, I am strictly querying #EmpSales table, making sure to filter for column “YearMonth.” Within the Pivot code, I select the needed data by using a self referencing join. We need a self join because our next step is to create a row of data that contains the SalesAmt for each EmpId’s salesId. Once we have this information we can use the pivot operator to perform the aggregation.

Results:

How about them apples? As you can see, the new pivot reflects the correct totals for each month. We can also create a version of this code that works with prior versions of SQL Server.

DECLARE @Pivot_Cols VARCHAR(MAX)

DECLARE @sql NVARCHAR(MAX)

SELECT 

    @Pivot_Cols = COALESCE(@Pivot_Cols + ',','') 

    + N'SUM(CONVERT(NUMERIC(9,2),CASE WHEN pc.[ColName] = ''YearMonth'' AND e.ColTxt =' 

    + QUOTENAME(e.ColTxt,'''') + ' THEN ' + ' e2.[ColTxt] ELSE NULL END)) AS ' 

    + QUOTENAME(e.ColTxt,'''') + CHAR(13)    

FROM #EmpSales e

WHERE e.ColId = 1

GROUP BY e.ColTxt

ORDER BY e.ColTxt

SET @sql = N'SELECT e.EmpId,' + CHAR(13)

      + @Pivot_Cols + CHAR(13) 

      + 'FROM #EmpSales e INNER JOIN #EmpSales e2 ON e.EmpId = e2.EmpId AND e.SalesId = e2.SalesId' + CHAR(13) 

      + 'INNER JOIN #Pivot_Columns pc ON pc.ColID = e.[ColId]' + CHAR(13)

      + 'INNER JOIN #Pivot_Columns pc2 ON pc2.ColId = e2.[ColId]' + CHAR(13)

      + 'WHERE pc.ColName = ''YearMonth'' AND pc2.ColName = ''SalesAmt''' + CHAR(13)

      + 'GROUP BY e.[EmpId]'

PRINT @sql

EXEC sp_executesql @sql

So there you have it. A completely dynamic method to pivot data. So what have we learned over these last few posts………….. well I hope that you have learned something :-). I hope that you learned pivoting data in SQL Server can be very simple. Pivoting data can sometimes become a little hairy, but is still a powerful and somewhat simplistic solution to implement. <step on soapbox> Hopefully, Microsoft will give us a pivot operator that is somewhat flexible and dynamic. A native dynamic pivot operator allows for more scalable code and reduced SQL injection risk.; however, I really doubt that we will get any dynamic pivot operator, in the near future</step off soapbox>. Anyway, I hope that you have enjoyed reading this series and that you can use this in your environment.

Until next time… happy coding.

Creating an HTML report dynamically, with conditional formatting and dynamic filtering

One of the things missing in SSMS is the ability to make “pretty” reports. Most user do not care for the plain text reports that are directly copied from the query window. In fact most of the “higher-ups” demand a report with more pizzazz. So how can we accommodate a “pretty” report, on a frequent schedule? One solution is to use SSRS, but we will be exploring a dynamic solution that uses dynamic sql and sp_send_dbmail. I will be using sp_send_dbmail() because it gives me the ability to put the results of a query into the body of email, or an attachment. How does a process that “automagically” creates and emails a pretty HTML report, with dynamic filtering and conditional formatting sound? I bet you are on the edge of your seat…. well maybe not :), but I bet you are at least thinking about how much time this could save you. So how do we do this? I started by breaking the logic down into three procedures. Two of these procedures are used to actually create and email the report and the third stored procedure is used to strip invalid characters out of the dynamic sql.

You can download all the code here: http://cid-6f041c9a994564d8.skydrive.live.com/self.aspx/.Public/SQL%20HTML%20Report/sp%7C_Create%7C_HTML%7C_Rpt.zip. Lets start with the first stored procedure. The first procedure strips invalid characters from a given input string. This stored procedure helps protect our data by reducing the risk of SQL injection attack. You can customize the regular expression to include characters you deem valid. These checks are done against the filtering and conditional formatting inputs. I chose to exclude all and only include valid characters versus choosing to include all and excluding specific characters. This is a more secure model and is equally configurable. The stored procedure accepts one parameter @str, which is an input string.

Parameters:

• @str – Input string to be stripped for invalid characters

USE master

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_RemoveChars]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

BEGIN

    DROP FUNCTION dbo.sp_RemoveChars

END

GO

CREATE FUNCTION dbo.sp_RemoveChars(@Str varchar(1000))

RETURNS VARCHAR(1000)

BEGIN

declare @NewStr varchar(500),

        @i int

set @i = 1        

set @NewStr = ''

while @i <= len(@str)

begin

    --grab digits or ( in regex) decimal

    if substring(@str,@i,1) LIKE '[0-9A-Z%\/.<>!=]' OR substring(@str,@i,1) = ' ' 

    begin

        set @NewStr = @NewStr + substring(@str,@i,1)

    end

    else

    begin

        set @NewStr = @NewStr

    end

    set @i = @i + 1

end

  RETURN Rtrim(Ltrim(@NewStr))

END

GO

The next stored procedure we will be creating is sp_Create_HTML_Rpt, which is the main report. This stored procedure actually generates the HTML. You should point this code at an table or view. There are lots of parameters here and I will go through each of them. Essentially this code gives you the flexibility to filter the data, create custom conditional formatting etc..

Parameters:

• @Schema – The schema of the object
• @object – The object being reported on. This can be a view or a table.
• @ColList – A comma delimited list of columns that you want to report on. The default is all columns for a given table/view.
• @FilterCol – The column that should filter the data
• @FilterOperator – The operand used to filter the data
• @FilterCriteria – The filter expression
• @HeaderBGColor – The color of the background for the header row.
• @Align – The alignment of the column data. The default is left.
• @ConditionColumn – This is the column that is used to conditionally apply formatting to a row.
• @ConditionOperator – This is the operand of the conditional expression
• @ConditionBGColor – The color of the background for the conditional expression, if met.
• @FontSize – The size of the body font.

--====================================================================================

-- CHANGE DB CONTEXT TO MASTER

--====================================================================================

USE master

GO

--====================================================================================

-- DROP THE SP IF IT ALREADY EXISTS

--====================================================================================

IF EXISTS(SELECT * FROM sys.procedures WHERE name = 'sp_Create_HTML_Rpt')

BEGIN

    DROP PROCEDURE sp_Create_HTML_Rpt;

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE sp_Create_HTML_Rpt

    @schema varchar(100) = 'dbo',

    @object varchar(100),

    @ColList varchar(max) = 'ALL',

    @FilterCol varchar(100) = NULL,

    @FilterOperator varchar(6) = '=',

    @FilterCriteria varchar(50) = NULL,

    @HeaderBGColor VARCHAR(7) = '#dcdcdc',

    @Align Varchar(10) = 'LEFT',

    @ConditionColumn varchar(100) = NULL,

    @ConditionOperator varchar(6) = '=',

    @ConditionCriteria varchar(100) = NULL,

    @ConditionalBGColor varchar(7) = '#cd5c5c',

    @FontSize varchar(2) = '10'

AS

BEGIN

set nocount on;

BEGIN TRY

BEGIN TRANSACTION

--====================================================================================

-- CHECK PARAMS FOR VALID DATA

--====================================================================================

--***********************************************************

-- BEGIN @SCHEMA CHECK

--***********************************************************

--if the schema does not exist raise an error

IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE [name] = @Schema) AND

    @Schema IS NOT NULL

BEGIN

    RAISERROR ('The specified schema does not exists. SP aborted.',10,1) WITH LOG

    RETURN 99 --ERROR

END

--***********************************************************

-- BEGIN @OBJECT CHECK

--***********************************************************

--if the object does not exist raise and error

IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = object_id(@object)) AND

    @Object IS NOT NULL

BEGIN

    --RAISEERORR WITH LOG

    RAISERROR ('The specified object name does not exists. SP aborted.',10,1) WITH LOG

    RETURN 99 --ERROR

END

--====================================================================================

-- GET FULL QUALIFIED NAME OF OBJECT

--====================================================================================

DECLARE @FullQualifiedName sysname

set @FullQualifiedName = '[' + DB_NAME() + '].'

set @FullQualifiedName = @FullQualifiedName + '[' + COALESCE(@schema,'dbo') + '].'

set @FullQualifiedName = @FullQualifiedName + '[' + @object + ']'

--***********************************************************

-- BEGIN @CONDITIONCOLUMN CHECK

--***********************************************************

DECLARE @ConditionColumnExists BIT

--if the condition column exists make the variable 1 else 0

IF EXISTS(

        select 1

        from sys.columns

        where name = @ConditionColumn and

              object_id = object_id(@FullQualifiedName))

    AND @ConditionColumn IS NOT NULL

BEGIN

    set @ConditionColumnExists = 1

END

ELSE

BEGIN

    set @ConditionColumnExists = 0

END

--***********************************************************

-- BEGIN @CONDITIONCOLUMNCRITERIA CHECK

-- The below function will strip out all invalid characters

-- valid chars are letters, numbers and the 

-- symbols "%","/',"\","."

--***********************************************************

IF LEN(@ConditionCriteria) > 0

BEGIN

set @ConditionCriteria = (select master.dbo.sp_RemoveChars(@ConditionCriteria))

END

--***********************************************************

-- BEGIN @FILTER CHECK

--***********************************************************

DECLARE @FilterColumnExists BIT

--if the condition column exists make the variable 1 else 0

IF EXISTS(

        select 1

        from sys.columns

        where name = @FilterCol and

              object_id = object_id(@FullQualifiedName))

    AND @FilterCol IS NOT NULL

BEGIN

    --filter column exists

    set @FilterColumnExists = 1

END

ELSE

BEGIN

    --filter column does not exist

    set @FilterColumnExists = 0

END

--***********************************************************

-- BEGIN @FILTERCRITERIA CHECK

-- The below function will strip out all invalid characters

-- valid chars are letters, numbers and the 

-- symbols "%","/',"\",".",”<”,”>”,”!”

--***********************************************************

IF LEN(@FilterCriteria) > 0

BEGIN

    set @FilterCriteria = (select master.dbo.sp_RemoveChars(@FilterCriteria))

END

--***********************************************************

-- BEGIN @Condition Operator CHECK

--***********************************************************

IF @ConditionOperator NOT IN('=','<','>','<=','>=','LIKE','BETWEEN','<>','!=')

BEGIN

    --RAISEERORR WITH LOG

    RAISERROR ('The specified filter operator is invlaid. SP aborted.',10,1) WITH LOG

    RETURN 99 --ERROR

END

--***********************************************************

-- BEGIN @Filter OPerator CHECK

--***********************************************************

IF @FilterOperator NOT IN('=','<','>','<=','>=','LIKE','BETWEEN','<>','!=')

BEGIN

    --RAISEERORR WITH LOG

    RAISERROR ('The specified filter operator is invlaid. SP aborted.',10,1) WITH LOG

    RETURN 99 --ERROR

END

--***********************************************************

-- BEGIN @ALIGN CHECK

--***********************************************************

--check for valid alignments, if the align var is set wrong

--set the alignment to left

IF @Align NOT IN ('Left','Center','Right') AND @Align IS NOT NULL

BEGIN

    SET @Align = 'Left'

END

--***********************************************************

-- BEGIN @FONTSIZE CHECK

--***********************************************************

IF @FontSize NOT LIKE '[0-9]'

BEGIN

    SET @FontSize = '10'

END

--if the font size is greater than 30 make it 30

IF @FontSize > 30

BEGIN

    SET @FontSize = '30'

END

--====================================================================================

-- DECLARE LOCAL VARIABLES

--====================================================================================

declare    @table_header varchar(max),

        @table_data varchar(max),

        @table_footer varchar(max),

        @Cols VARCHAR(max),

        @sql nvarchar(max),

        @table_StyleSheet varchar(max),

        @params nvarchar(500),

        @x XML

--====================================================================================

-- INITIALIZE LOCAL VARIABLES

--====================================================================================

set @table_header = ''

set @table_data = ''

set @table_footer = ''

SET @Cols = ''

set @sql = ''

set @table_StyleSheet = ''

set @params = N'@bgColor varchar(7)' +

                CASE WHEN @FilterColumnExists = 1 THEN ', @Filter varchar(100)' ELSE '' END

SET @x = '<i>' + REPLACE( @ColList, ',', '</i><i>') + '</i>'

--***********************************************************************

-- SETUP DELIMITED COLUMN LIST, WITH TABLE DESC TAGS

--***********************************************************************

IF @ColList = 'ALL'

BEGIN

select  @Cols = COALESCE(@Cols,'') +

    '''<td ' + COALESCE(@Align,'LEFT') + 

    '>'' + CONVERT(VARCHAR(200),COALESCE(' + c.Name + ','''')) + ''</td>'' + '

from sys.columns c

where object_id = object_id(@FullQualifiedName)

order by column_id

END

ELSE

BEGIN 

select  @Cols = COALESCE(@Cols,'') +

    '''<td Align= ' + COALESCE(@Align,'LEFT') + 

    '>'' + CONVERT(VARCHAR(200),COALESCE(' + c.Name + ','''')) + ''</td>'' + '

from sys.columns c

    inner join(

        SELECT x.i.value('.', 'VARCHAR(100)') as [ColName]

        FROM @x.nodes('//i') x(i)

) as Cols

    on c.name = LTRIM(RTRIM(Cols.ColName))

where object_id = object_id(@FullQualifiedName)

order by column_id

END

--====================================================================================

-- CREATE TEMP TABLE TO HOUSE RESULTS

--====================================================================================

CREATE TABLE #Results(

HTML_Seq VARCHAR(2),

TagType VARCHAR(20),

TagText VARCHAR(MAX)

)

--====================================================================================

-- BEGIN POPULATING TEMP TABLE RESULTS, WITH HTML DATA

--====================================================================================

--***********************************************************

-- BEGIN INSERT STYLESHEET

--***********************************************************

INSERT INTO #Results (HTML_Seq,TagType,TagText) 

VALUES (1,'STYLESHEET','<style type="text/css">');

INSERT INTO #Results (HTML_Seq,TagType,TagText) 

VALUES (1,'STYLESHEET','table {font-size: ' + COALESCE(@FontSize,'10') + 'pt;}');

INSERT INTO #Results (HTML_Seq,TagType,TagText) 

VALUES (1,'STYLESHEET','</style>');

--***********************************************************

-- BEGIN INSERT TABLE HEADER

--***********************************************************

INSERT INTO #Results (HTML_Seq,TagType,TagText) 

VALUES (2,'HEADER','<HTML><BODY><table border=1 cellpadding=5 cellspacing=1>');

INSERT INTO #Results (HTML_Seq,TagType,TagText)

VALUES (2,'HEADER','<tr bgcolor="' + COALESCE(@HeaderBGColor,'#FFFFFF') + '">');

IF @ColList = 'ALL'

BEGIN

    INSERT INTO #Results (HTML_Seq,TagType,TagText)

    select 2, 'HEADER', '<th>' + c.name + '</th>'

    from sys.columns c

    where object_id = object_id(@FullQualifiedName)

    order by column_id

END

ELSE

BEGIN 

    INSERT INTO #Results (HTML_Seq,TagType,TagText)

    select 2, 'HEADER', '<th>' + c.name + '</th>'

    from sys.columns c

        inner join(

            SELECT x.i.value('.', 'VARCHAR(100)') as [ColName]

            FROM @x.nodes('//i') x(i)

    ) as Cols

        on c.name = LTRIM(RTRIM(Cols.ColName))

    where object_id = object_id(@FullQualifiedName)

    order by column_id

END

INSERT INTO #Results (HTML_Seq,TagType,TagText) 

VALUES (2, 'HEADER','</tr>');

--***********************************************************

-- BEGIN INSERT TABLE BODY

--***********************************************************

SET @sql = N'insert into #Results (HTML_Seq,TagType,TagText) 

select ''3'', ''BODY'', ' +

case when @ConditionColumnExists = 1 then

    'case when [' + COALESCE(@ConditionColumn,'') + '] ' + @ConditionOperator + '''' + @ConditionCriteria + ''' then 

        ''<tr bgcolor='' + '''' +  COALESCE(@bgColor,''"#FFFFFF"'') + '''' + ''>'' + '

             + COALESCE(@Cols,'') + ' + ''</tr>'' 

     else ''<tr>'' + ' + COALESCE(@Cols,'') + ' + ''</tr>''

     end'

else

    '''<tr>'' + ' + COALESCE(@Cols,'') + ' + ''</tr>'''

end + '

From ' + @FullQualifiedName +

case when @FilterColumnExists = 1 THEN 

' WHERE ' + COALESCE(@FilterCol,'') + space(1) + @FilterOperator + space(1) + '@Filter'

ELSE '' END

IF @FilterColumnExists = 0

BEGIN

EXECUTE sp_executesql    @sql

                        ,@params

                        ,@bgColor = @ConditionalBGColor

END

IF @FilterColumnExists = 1

BEGIN

EXECUTE sp_executesql    @sql

                        ,@params

                        ,@bgColor = @ConditionalBGColor

                        ,@Filter = @FilterCriteria

END

--print @Cols

--print @FilterCriteria

--***********************************************************

-- BEGIN INSERT TABLE FOOTER

--***********************************************************

INSERT INTO #Results (HTML_Seq,TagType,TagText) 

VALUES (4, 'FOOTER','</table><BODY><HTML>');

--====================================================================================

-- FINAL SELECT TO RETURN THE NEEDED DATA

--====================================================================================

select TagText

from #Results

order by HTML_Seq

COMMIT TRANSACTION

END TRY

BEGIN CATCH

    SELECT

        ERROR_NUMBER() AS ErrorNumber,

        ERROR_SEVERITY() AS ErrorSeverity,

        ERROR_STATE() AS ErrorState,

        ERROR_PROCEDURE() AS ErrorProcedure,

        ERROR_LINE() AS ErrorLine,

        ERROR_MESSAGE() AS ErrorMessage;

    ROLLBACK TRANSACTION

END CATCH

END

GO

--====================================================================================

-- Mark the sp as a system object, so you can call it from any db

-- and get the local system metadata.

--====================================================================================

EXEC sys.sp_MS_marksystemobject 'sp_Create_HTML_Rpt'

GO

Now that we have our HTML code, we can create the stored procedure that emails the report. Note: This stored procedure is just a wrapper that calls sp_Create_HTML_Rpt, along with sp_send_dbmail.

Parameters:

• @Email_To – Email addresses to send the report
• @Email_Body – The text of the email, which proceeds the HTML report.
• @Email_Subject – The subject of the email
• All remaining parameters have been discussed

USE master

GO

IF EXISTS(SELECT * FROM sys.procedures WHERE name = 'sp_Mail_HTML_Rpt')

BEGIN

    DROP PROCEDURE sp_Mail_HTML_Rpt;

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE sp_Mail_HTML_Rpt

    --@Email_Profile VARCHAR(25),

    @Email_To VARCHAR(100),

    @Email_Body VARCHAR(max),

    @Email_Subject VARCHAR(100),

    @schema varchar(100) = 'dbo',

    @object varchar(100),

    @ColList varchar(max) = 'ALL',

    @FilterCol VARCHAR(100) = NULL,

    @FilterOperator varchar(6) = '=',

    @FilterCriteria varchar(100) = NULL,

    @HeaderBGColor VARCHAR(7) = '#dcdcdc',

    @Align Varchar(10) = 'LEFT',

    @ConditionColumn varchar(100) = NULL,

    @ConditionOperator varchar(6) = '=',

    @ConditionCriteria varchar(100) = NULL,

    @ConditionalBGColor varchar(7) = '#cd5c5c',

    @FontSize varchar(2) = '10'

AS

BEGIN

    BEGIN TRY

        BEGIN TRANSACTION

        --==========================================================================

        -- DECLARE LOCAL VARIABLES

        --==========================================================================

        DECLARE @Email_Query NVARCHAR(500),

                @db sysname

        --==========================================================================

        -- INITIALIZE LOCAL VARIABLES

        --==========================================================================

        SET @db = db_name()

        --********************************************************

        -- Execute sp to create HTML report

        --********************************************************

        SET @Email_Query = 

        N'EXECUTE sp_Create_HTML_Rpt

           @schema = ''' + COALESCE(@schema,'dbo') + '''

          ,@object = ''' + @object + '''

          ,@ColList = ''' + COALESCE(@ColList,'ALL') + '''

          ,@FilterCol = ''' + COALESCE(@FilterCol,'') + '''

          ,@FilterOperator = ''' + COALESCE(@FilterOperator,'=') + '''

          ,@FilterCriteria = ''' + COALESCE(@FilterCriteria,'') + '''

          ,@HeaderBGColor = ''' + COALESCE(@HeaderBGColor,'#dcdcdc') + '''

          ,@Align = ''' + COALESCE(@Align,'LEFT') + '''

          ,@ConditionColumn = ''' + COALESCE(@ConditionColumn,'') + '''

          ,@ConditionOperator = ''' + COALESCE(@ConditionOperator,'=') + '''

          ,@ConditionCriteria = ''' + COALESCE(@ConditionCriteria,'') + '''

          ,@ConditionalBGColor = ''' + COALESCE(@ConditionalBGColor,'#cd5c5c') + '''

          ,@FontSize = ''' + COALESCE(@FontSize,'10') + ''''

        --==========================================================================

        -- EXECUTE SEND MAIL TASK

        --==========================================================================

        --you can make an html attachment

        EXEC msdb.dbo.sp_send_dbmail

                --@profile_name = @Email_Profile,

                @recipients = @Email_To,

                @body = @Email_Body,

                @query = @Email_Query,

                @execute_query_database = @db,

                @attach_query_result_as_file = 0,

                @query_result_header = 0,

                @exclude_query_output = 1,

                @subject = @Email_Subject,

                @body_format = 'HTML',

                @importance = 'NORMAL',

                @append_query_error =1,

                @query_no_truncate = 1

        COMMIT TRANSACTION

    END TRY

    BEGIN CATCH

        SELECT

            ERROR_NUMBER() AS ErrorNumber,

            ERROR_SEVERITY() AS ErrorSeverity,

            ERROR_STATE() AS ErrorState,

            ERROR_PROCEDURE() AS ErrorProcedure,

            ERROR_LINE() AS ErrorLine,

            ERROR_MESSAGE() AS ErrorMessage;

    ROLLBACK TRANSACTION

    END CATCH

END

Now that you have the code you can execute it by issuing a statement similar to this.

EXECUTE [sp_Mail_HTML_Rpt] 

   --@Email_Profile = null  --db mail email profile (not needed)

   @Email_To = 'ahaines@somedomain.com' --send email to

  ,@Email_Body = 'Attached is the daily scheduled job report. Please review. <br> <br>' --body text

  ,@Email_Subject = 'Schedule Job Report' --subject

  ,@schema = 'dbo' --object schema

  ,@object =  'vw_ScheduleJobReport' --object name

  ,@ColList = 'ALL' -- specify column list comma delimited. All is default

  ,@FilterCol = null --column to filter data on

  ,@FilterOperator = null --filter operand

  ,@FilterCriteria = null -- filter criteria

  ,@HeaderBGColor = NULL --header background color

  ,@Align = null --detail align

  ,@ConditionColumn = 'descr' --column for conditional formatting

  ,@ConditionOperator = '=' --conditional operator

  ,@ConditionCriteria = 'FAILED' --criteria to highlight column

  ,@ConditionalBGColor = 'RED' --conditional background color

  ,@FontSize = NULL -- detail font size

There you have it. A stored procedure that will take a given table/view and automatically generate and email an HTML report. As you can see this code is easily customizable and I invite you to do so.