Microsoft Flow With SQL Server

Introduction

In this blog, you will learn how we can send an email notification through Microsoft flow and SQL Server.

Purpose

We are going to create a Microsoft flow that will get the data using an SQL stored procedure and send the result via email.

The stored procedure (If you want to send the data in HTML format).

DECLARE @CureentDate DATETIME
DECLARE @tableHTML NVARCHAR(MAX);
SET @CureentDate =GETDATE();
SET @Month=FORMAT(@CureentDate, 'MMMM')
SET @startHtml='<html><body>'
SET @endHtml='</body></html>'
SELECT (FirstName+' '+LastName) AS Name,BirthDate AS BirthDate
INTO #Sheet1
From Employee WITH(NOLOCK)
Set @BirthDaytable=N'<H3>Birth Date in '+@Month +'</H3>'+ '<table border="1" style="border:1px solid #77bfe4;font-family:Tahoma; font-weight:normal; font-size:12px;" cellpadding="4" cellspacing="0">' + '<tr bgcolor="#E0E0E0" style="font-family:Tahoma; font-weight:bold; font-size:12px;"><td><center>Name</center></td><td><center>BirthDate</center></td><td></td>
</tr>' + CAST((
SELECT
td = K.Name
,''
,td =CONVERT(CHAR(11),K.BirthDate,113)
,''
FROM #Sheet1 AS K
ORDER BY K.BirthDate DESC
FOR XML PATH('tr')
,TYPE
--) AS NVARCHAR(MAX)) + '</table><br>Thank you</br></body></html>'
) AS NVARCHAR(MAX)) + '</table>'
SET @tableHTML=@startHtml+@BirthDaytable+@endHtml
Select @tableHTML

Now we are going to create flow. When you log into the Microsoft flow, the below screen will show. Then click on Create.

So now we are going to use scheduled flow. It will give the below four options in the image.

When we click the scheduled flow below screen will come, you can see it gives multiple options for scheduling.

First, give the name of the scheduler flow name, then click on the New Step button.

Select the action that we need to perform to execute an SQL stored procedure. I am selecting Execute Stored procedure in the new step

Note: Premium connectors have some charges.

In 'execute a stored procedure', we can manage the SQL connection and add the new SQL connection using the rightmost three Dots

Then, select the connection string Name as whatever server name you saved when you added a connection. Select the DB name and Procedure Name that you have created.

One more step; send an email (action), and in the email body section, you can easily select the output of the procedure, as shown in the below image:

If you want to directly execute the SQL statement (script) then you can select Execute a SQL Query Action and set the output of the result on an Email body.

Now your flow is done. Save the flow and you can test the flow as shown in the below image: