Stored Procedure to send email using sp_send_dbmail

This stored procedure was used to send email using the system stored procedure sp_send_dbmail. The client email system was Lotus 6.5 and the script was installed/test with SQL 2005.

This code can be downloaded from this google document site.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Early_Buyer_Email]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--usp_Early_Buyer_Email ''0000037579'', ''06/01/2007'', ''SRI'', ''RADIO_14''

CREATE PROCEDURE [dbo].[usp_Early_Buyer_Email]
@purchase_order_no varchar(10),
@purchase_order_dt datetime,
@user_id varchar(10),
@device_id varchar(10)
AS
/****************************************************************************
HJ Consultant Srinivasan Prasanna

Client Company ABC

Proc Name usp_Early_Buyer_Email
Purpose This SP will be used to send out emails for Early PO''s
Date Created 5/23/2007
Version 1.0
Date Changed
******************************************************************************/
declare @sql varchar(5000)
declare @BUYER_ID varchar(100)
declare @cc_email varchar(100)
declare @msg varchar(1000)
declare @po_dt varchar(10)
declare @today varchar(10)

SET NOCOUNT ON
-- convert the datetime into varchar because the concatenation needs to email this message
set @po_dt = convert(varchar(10),@purchase_order_dt,101)
set @today = convert(varchar(10),getdate(),101)
-- this is hard coded value per the client''s request
set @cc_email = ''srinivasan@prasanna.com''
-- create temp table and drop it immediately
create table #temp
(colA varchar(100))

select @sql = ''insert into #temp select * from OPENQUERY (PeopleSoft, ''''SELECT BUYER_ID FROM PS_PO_HDR WHERE PO_ID = '''''''''' + @purchase_order_no + '''''''''''''')''
exec(@sql)

-- this means there is a buyer name in the database

if (select count(*) from #temp) > 0
begin
select @BUYER_ID = colA from #temp
end

-- drop the temp table
drop table #temp

select @BUYER_ID = email_id from t_buyer_email

where BUYER_ID = @BUYER_ID

select @msg = ''The Receipt date for this PO Number - '' + @purchase_order_no + '' is :
'' + @po_dt + '' . The user '' + @user_id + '' tried to receive this PO Number using
the device '' + @device_id +'' on '' + @today

EXEC msdb.dbo.sp_send_dbmail
@profile_name = ''HJ'',
@recipients = @BUYER_ID ,
@copy_recipients = @cc_email,
@body = @msg,
@importance = ''High'',
@subject = ''Early date PO - The PO cannot be received'' ;

-- at this point the email is complete. Now create a log for the PO which was not received on time.

insert into t_email_log
(buyer_email_id, cc_email_id, po_id, po_date, device_id, user_id, dt_timestamp)
select @BUYER_ID, @cc_email, @purchase_order_no, @purchase_order_dt, @device_id, @user_id, getdate()

END
GO