trjhtr

I am working on an ASP.net project which use Stored Procedures. I am using SSMS.

The project was started years ago, and there are several SP created by other developers.

I need to write an SP to send an SMS, and my TL give me this SP and said this one does the same.

ALTER PROCEDURE [dbo].[SendSMS] 
 -- Add the parameters for the stored procedure here 
 @CompanyID as int 
AS 
BEGIN 
 -- SET NOCOUNT ON added to prevent extra result sets from 
 -- interfering with SELECT statements. 
 SET NOCOUNT ON; 
create table #MailList(
 CustomerId int 
 ,DepartmentID int 
 ,DepartmentName varchar(50) 
 ,InvocieId int 
 ,InvAmount numeric(18,2) 
 ,SettledAmt numeric(18,2) 
 ,BalanceAmt numeric(18,2) 
 ,Age int 
 ,Due char(10) 
 ,Processed bit 
 ,CurrencyName varchar(50) 
 ,Symbol varchar(20)
 ,Invoicedate datetime 
 ,Invoiceno varchar(50) 
 ,Referenceno varchar(100) 
 ,remarks varchar(1000)
) 

create table #Departments(
 departmentid int 
) 


insert into #MailList select * from dbo.EmailList 

 declare @customerid as int; 
 declare @amount as numeric(18,2) 
 declare @age as int 
 declare @CustomerName as varchar(100) 
 declare @ContactName1 as varchar(100) 
 declare @DuePeriod as int 
 declare @EmailID as varchar(500) 
 declare @currencysymbol as varchar(20) 
 declare @body1 varchar(5000)
 declare @Message varchar(5000) 
 declare @subject1 varchar(1000) 
 declare @table as varchar(200) 
 declare @Signature as varchar(2000) 
 declare @Address1 varchar(250) 
 declare @Address2 varchar(250) 
 declare @Address3 varchar(250) 
 declare @Address varchar(1000) 
 declare @ccEmail varchar(2000) 
 declare @cccEmail varchar(2000) 
 declare @CopycEmail varchar(2000) 
 declare @departmentid int 
 declare @departmentname varchar(50) 
 declare @Profile as varchar(100) 
 declare @Divisionhead as varchar(150) 
 declare @CompanyName as varchar(250)
 declare @CompanyTelephone as varchar(50)
 declare @CompanyFax as varchar(50)
 declare @Companyemail as varchar(50)
 declare @CompanyAddress1 as varchar(250)
 declare @CompanyAddress2 as varchar(250)
 declare @CompanyAddress3 as varchar(250)
 declare @XMLEmailList as xml 
 declare @CustomerStatement as varchar(max) 

set @profile='Automated SMS Service' 
while((select count(*) from #MailList )<>0 ) 
 begin 
 set @amount=0 
 set @EmailID='' 
 select 
 @customerid=max(customerid) 
 from #MailList 
 select 
 @EmailID=EmailId,
 @CustomerName=CustomerName,
 @Subject1=contactPhone,
 @ContactName1=ContactName,
 @Address1=Address1,
 @Address2=Address2,
 @Address3=Address3,
 @DuePeriod=Dueperiod 
 from dbo.aCustomerMaster where CustomerID=@customerid and CompanyID=@CompanyId 

 ---filtering the #MailList table by customerwise and assigning to another table #MailListCustomerwise 
 select * into #MailListCustomerwise from dbo.EmailList where customerid=@customerid 
 insert into #Departments select distinct departmentid from #MailListCustomerwise 

 while((select count(*) from #MailListCustomerwise)<>0) 
 begin 
 set @amount=0
 select 
 @departmentid=max(departmentid)
 from #Departments 
 ------for preparing mail for the bills due before 10 days before due date
 select 
 @departmentname=DepartmentName,
 @amount=sum(balanceamt),
 @currencysymbol=symbol 
 from dbo.#MailList 
 where customerid=@customerid and age>@DuePeriod and departmentid =@departmentid
 group by symbol,DepartmentName 
 if @amount>0 
 begin 
 set @Signature=' Should you have any concerns related to releasing the payment, Please contact US on +918888888888.'
 set @body1 = 'Dear '+isnull(@ContactName1,' ')+', Hope you are keeping well. Kind reminder to make the payment for '+@currencysymbol +' ' +cast(@amount as varchar(50)) +'.Wishing you a good day.'+@Signature 


 EXEC msdb.dbo.sp_send_dbmail @recipients='sms@comcare.com', 
 @subject = @subject1, 
 @body = @body1, 
 @body_format = 'TEXT', 
 @profile_name=@profile,@importance ='High' 
 end 

 delete from #MailListCustomerwise where departmentid=@departmentid 
 delete from #Departments where departmentid =@departmentid 
 end 
 delete from #Departments 
 drop table #MailListCustomerwise 
 delete from #MailList where customerid=@customerid 

 end 
END 

This SP was written in 2009, and I am wondering should I change anything in this or can I use this as it is and change the variables and tables to work with my needs?

I am new to Stored Procedures, So please help me.