SQL sproc with output variable

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL sproc with output variable

Author

Topic

nietzky
Yak Posting Veteran

75 Posts

Posted - 2013-05-09 : 14:55:32

I have a table that looks like this:

EMAIL
john.smith@domain.net
user121311@domain.net
user1567@domain.net;user6562@domain.net
user1345345@domain.net;john.smith@domain.net;john.green@domain.net
user768@domain.net
cf343@domain.net
fghu423423@domain.net
user12199@domain.net
user10000@domain.net;user12199@domain.net

How can I dump all of these addresses into a single output variable ex. VARCHAR(8000). or whatever value is safe to use with at least 50 addresses

All of the emails would need to be distinct ex. john.smith@domin.net would show only once, as I will use the variable in application layer to email folks. Some of these values contain 1 email address some of them may contain up to 10 or so. They are always seperated by ";" character.

I would like to get all the unique emails in this format

user1@domain.net;user121311@domain.net;user1567@domain.net;user6562@domain.net;user1345345@domain.net;john.smith@domain.net;john.green@domain.net;user768@domain.net;cf343@domain.net;fghu423423@domain.net
user12199@domain.net;user10000@domain.net;user12199@domain.net

they would be returned as output variable.

Thank you.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-09 : 15:10:43

[code]DECLARE @emails VARCHAR(8000);
SELECT @emails = STUFF((SELECT DISTINCT ';' + email FROM YourTable FOR XML PATH('')),1,1,'');
[/code]

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-09 : 15:14:53

Something like this:

[CODE]

DECLARE @EmailString varchar(max);

SELECT
@EmailString = STUFF( (SELECT ';' + name1 from (SELECT DISTINCT [EMAIL] as name1 FROM TABLE) as A
FOR XML PATH('')), 1, 1, '');
[/CODE]

James K's solution is better

nietzky
Yak Posting Veteran

75 Posts

Posted - 2013-05-09 : 15:42:28

Thank you James amd MuMu! It works.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources