Sending HTML Table in Email Using SQL Server

•    Today I am going explain how we can send html table email from SQL server.  One of my client have requirement they want employee’s list are highlight with some background color when employee‘s basic salary is more than 15000 in Email.

•  Instead of going to application level I will manage things from SQL server and I have used SQL dbmail functionality which is available in SQL Server full version.

•  SQL DB email function, first we need to create profile name in SQL Server and need to configure with our domain.

•  First I have declare tables

DECLARE @tableHTML NVARCHAR(MAX)

• From Above query I have get Employees in html tables structure which include Basic Salary and  I have took those employee in “Red” background whose Basic Salary is more than 15,000 and rest of employee have white background.

• Generated  “tableHTML” looks like following in HTML form

• Now I need to send email of above template from SQL Server. We can use following query to send email to receipt

EXEC msdb.dbo.sp_send_dbmail

    @profile_name='Nilay',

    @recipients = 'qa1@orangewebtech.com',

    @from_address = 'nilay@orangewebtech.com',

    @subject = 'Employee List',

    @body = @tableHTML,

    @body_format = 'HTML';

• We can also used SQL Jobs for email list of employees on specified duration.

Rules(BCD) for Validate Regular Expression

B- There are three types of brackets used in regular expression.

      Square Bracket    - Specify the character which needs to be matched

      Curly Bracket “{” - Specify how many character

       Para thesis          - Specify for Grouping

C -Carrot “^” the start of regular expression

D –Dollar “$” the end of the regular expression

Eg. ^[a-g]{3}$

Above example will allow to enter character between a to g with maximum three character.

REPLACE SPECIAL CHARACTER FROM STRING IN SQL

• In my project, I have one .csv file and I load csv file into SQL server tables where SQL server table column have some time NVARCHAR(which include special character) columns

• Some time it got some special character in my table column (ex. In my alphanumeric employee code have ‘#’ or ‘?/’), I need to replace all special character with no space

•  I have option with replace function to remove unwanted space but it seems I need write series of replace function.

SELECT REPLACE( REPLACE( REPLACE( REPLACE(@str, '/', '' ), '#', '' ));

•  I need to create dynamic function which replaces all special character with no space.

      DECLARE @str VARCHAR(25)

      SET @str = '(NIL)AY-MISTRY''l123'

     SELECT PATINDEX( '%[~,@,#,$,%,&,*,(,)]%', @str )

     WHILE PATINDEX( '%[~,@,#,$,%,&,*,(,)]%', @str ) > 0

    SET @str = Replace(REPLACE( @str, SUBSTRING( @str, PATINDEX(               '%[~,@,#,$,%,&,*,(,)]%', @str ), 1 ),''),'-',' ')

     SELECT @str

• Above query we can also convert into SQL function to return no space string value.

• More about PATINDEX (Ref. Link)

  http://technet.microsoft.com/en-us/library/ms188395%28v=sql.105%29.aspx