Split Comma Separated List Without Using a Function in SQL Server

DECLARE @t TABLE
(
EmployeeID varchar(100),
Certs VARCHAR(8000)
)
INSERT @t VALUES ('Param1:','B.E.,MCA, MCDBA, PGDCA'), ('Param2:','M.Com.,B.Sc.'), ('Param1:','M.Sc.,M.Tech.')

SELECT EmployeeID,
EmployeeID+LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS Certs
FROM
(
SELECT EmployeeID,CAST('<XMLRoot><RowData>' + REPLACE(Certs,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
FROM   @t
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)


Output:

How to configure database mail in sql server 2016 and above

GO
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO

EXECUTE msdb.dbo.sysmail_add_profile_sp 
    @profile_name = 'TestDB_Mail', 
    @description = 'Profile used for sending outgoing notifications using Gmail.' ; 
GO

-- Grant access to the profile to the DBMailUsers role  
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp 
    @profile_name = 'TestDB_Mail', 
    @principal_name = 'public', 
    @is_default = 1 ;
GO

-- Create a Database Mail account  
EXECUTE msdb.dbo.sysmail_add_account_sp 
    @account_name = 'Gmail', 
    @description = 'Test mail.', 
    @email_address = 'XXXXXXXX', 
    @display_name = 'Automated Mailer', 
    @mailserver_name = 'smtp.gmail.com',
    @port = 25,
    @enable_ssl = 1,
    @username = 'XXXXXXX',
    @password = 'XXXXXX' ; 
GO

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp 
    @profile_name = 'TestDB_Mail', 
    @account_name = 'Gmail', 
    @sequence_number =1 ; 
GO

--To Send email

EXEC msdb.dbo.sp_send_dbmail
     @profile_name = 'TestDB_Mail',
     @recipients = 'XXXXXX',
     @body = 'The database mail configuration was completed successfully1.',
     @subject = 'Automated Success Message';
GO

----Rrecall Everything

EXECUTE msdb.dbo.sysmail_delete_profileaccount_sp @profile_name = 'TestDB_Mail'
EXECUTE msdb.dbo.sysmail_delete_principalprofile_sp @profile_name = 'TestDB_Mail'
EXECUTE msdb.dbo.sysmail_delete_account_sp @account_name = 'Gmail'
EXECUTE msdb.dbo.sysmail_delete_profile_sp @profile_name = 'TestDB_Mail'
GO

Send email from SQL Server stored procedure

Step 1: Enable Reconfigure in SQL by using below script.

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'Ole Automation Procedures', 1;

GO

RECONFIGURE;

GO

Step 2: Create Stored procedure by using below script

CREATE PROCEDURE [dbo].[sp_send_mail]

@from varchar(500) ,

@to varchar(500) ,

@subject varchar(500),

@body varchar(4000) ,

@bodytype varchar(10),

@output_mesg varchar(10) output,

@output_desc varchar(1000) output

AS

DECLARE @imsg int

DECLARE @hr int

DECLARE @source varchar(255)

DECLARE @description varchar(500)

EXEC @hr = sp_oacreate 'cdo.message', @imsg out

--SendUsing Specifies Whether to send using port (2) or using pickup directory (1)

EXEC @hr = sp_oasetproperty @imsg,

'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").value','2'

--SMTP Server

EXEC @hr = sp_oasetproperty @imsg, 

  'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").value', 

  'smtp.gmail.com' 

--UserName

EXEC @hr = sp_oasetproperty @imsg, 

  'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").value', 

  'sender@gmail.com' 

--Password

EXEC @hr = sp_oasetproperty @imsg, 

  'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").value', 

  'xxxxxx' 

--UseSSL

EXEC @hr = sp_oasetproperty @imsg, 

  'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpusessl").value', 

  'True' 

--PORT 

EXEC @hr = sp_oasetproperty @imsg, 

  'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport").value', 

  '465' 

--Requires Aunthentication None(0) / Basic(1)

EXEC @hr = sp_oasetproperty @imsg, 

  'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").value', 

  '1' 

EXEC @hr = sp_oamethod @imsg, 'configuration.fields.update', null

EXEC @hr = sp_oasetproperty @imsg, 'to', @to

EXEC @hr = sp_oasetproperty @imsg, 'from', @from

EXEC @hr = sp_oasetproperty @imsg, 'subject', @subject

-- if you are using html e-mail, use 'htmlbody' instead of 'textbody'.

EXEC @hr = sp_oasetproperty @imsg, @bodytype, @body

EXEC @hr = sp_oamethod @imsg, 'send', null

SET @output_mesg = 'Success'

-- sample error handling.

IF @hr <>0 

SELECT @hr

BEGIN

EXEC @hr = sp_oageterrorinfo null, @source out, @description out

IF @hr = 0

BEGIN

--set @output_desc = ' source: ' + @source

set @output_desc =  @description

END

ELSE

BEGIN

SET @output_desc = ' sp_oageterrorinfo failed'

END

IF not @output_desc is NULL

SET @output_mesg = 'Error'

END

EXEC @hr = sp_oadestroy @imsg

Step 3: Execute Stored procedure (to send email to sender) by using below script

DECLARE @out_desc varchar(1000),

@out_mesg varchar(10)

EXEC sp_send_mail 'sender@gmail.com',

'receiver@gmail.com',

'Hello', 

'<b>This is s Test Mail</b>',

'htmlbody', @output_mesg = @out_mesg output, @output_desc = @out_desc output

PRINT @out_mesg

PRINT @out_desc