Best way to handle email addresses inside stored procedures

Question

Best way to handle email addresses inside stored procedures

dndaughtery

SSChampion

Points: 11290
More actions
April 6, 2011 at 11:20 am

#234405

I have been tasked with updating stored procedures where many email addresses are hard coded in the code. Which is the best way to do this and be able to update it when employees come and go? Right now I'm thinking of setting up a table in one database to hold all email addresse combinations and setting the value of a local variable for the email addresses with a query inside the sproc so that if someone is hired and needs to be added then I can add just update the record for that Sproc. Another idea I have is to have a role column added and set a role for each distinct email addersses in the email_Address column. I was just wondering if SQL Server had any built in functionality to handle this type of thing.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

SQLHeap Hall of Fame Points: 3572 More actions · Answer 1

The best way would be to create an email address table or add a column to an existing table for emails. The sp should go to the table.column for the data, hard coded email addy's :blink: is not exactly a great idea.

There is an exception to every rule, except this one...

Lowell SSC Guru Points: 323518 More actions · Answer 2

well as a start, you could limit it to only procs/functions/triggers that have strings that might be an email;

this certainly found the two objects i have that have a hardcoded email address in them:

select * from sys.sql_modules where definition like '%[A-Z]@_%'

from there, you might search just those procs, maybe by splitting the definition on spaces and CrLf/ word breaks;

that might be a single column table that you can filter for the same LIKE above to extract the emails.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323518 More actions · Answer 3

like this: just tested it:

select * from

(

select definition,myf.* from sys.sql_modules

cross apply dbo.DelimitedSplit8K(definition,' ') myf

where definition like '%[A-Z]@_%'

) myAlias

where Item like '%[A-Z]@_%'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

dndaughtery SSChampion Points: 11290 More actions · Answer 4

Yeah, I've already found everything with emaill addresses by looking for the email extension we use in SQL Search from redgate. I was just hoping that maybe SQL Server might have some built in functionality for email roles.

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 5

Hi,

I think you may do in this way

declare @Email table (id int identity(1,1),email varchar(75),status bit)

insert into @Email

values

('Email1@Email.com',1),

('Email2@Email.com',0),

('Email3@Email.com',0),

('Email4@Email.com',1),

('Email5@Email.com',0),

('Email6@Email.com',1)

Select * from @Email where status=0 /*0- Acive 1-In-Active*/

declare @ip varchar(5000) set @ip=''

select @ip =@ip +email+';' from @Email where status=0

select @ip

You can pass @ip as active email-id's

Thanks
Parthi

Jeremy Leasor SSC Veteran Points: 234 More actions · Answer 6

Depending on how far you want to go. You could have an email table with the list of email addresses in it, then have a one to many relation ship with that table to assoicate email groups for common email groups that is sent by your application.

That way you could just update the emails associated with that group.