How To Send Email From Stored Procedure In Sql Server

To send an email from a stored procedure in SQL Server, you can use the built-in sp_send_dbmail stored procedure. You need to configure SQL Server to enable Database Mail feature before you can use this procedure. Here is an example of how to send an email from a stored procedure:

    
      -- Sample stored procedure to send an email
      CREATE PROCEDURE dbo.SendEmail
          @RecipientName   VARCHAR(100),
          @RecipientEmail  VARCHAR(100),
          @Subject         NVARCHAR(255),
          @Body            NVARCHAR(MAX)
      AS
      BEGIN
          SET NOCOUNT ON;

          -- Send the email using sp_send_dbmail procedure
          EXEC msdb.dbo.sp_send_dbmail
              @profile_name     = 'YourDatabaseMailProfile',
              @recipients       = @RecipientEmail,
              @subject          = @Subject,
              @body             = @Body,
              @body_format      = 'HTML';
      END
    
  

In this example, the stored procedure named SendEmail takes four parameters: RecipientName, RecipientEmail, Subject, and Body. You need to replace YourDatabaseMailProfile with the actual name of your Database Mail profile.

The @body_format parameter is set to ‘HTML’ to allow sending HTML content in the email body. You can change it to ‘TEXT’ if you want to send plain text email.

To execute the above stored procedure and send an email, you can use the following SQL statement:

    
      -- Execute the stored procedure to send the email
      EXEC dbo.SendEmail
          @RecipientName   = 'John Doe',
          @RecipientEmail  = 'johndoe@example.com',
          @Subject         = 'Test Email',
          @Body            = '<h1>Hello!</h1><p>This is a test email from SQL Server.</p>';
    
  

In the above example, an email is sent to ‘johndoe@example.com’ with the subject ‘Test Email’ and the HTML content specified in the @Body parameter. You can customize the recipient, subject, and content as needed.