Using Inner Join on Comma-Separated Values in Stored Procedure

Question

I have a table UserMaster as follow...(only required columns are shown)

UserID UserName EmailID
---------------------------------
1000   amol     [email protected]
1001   mahesh   [email protected]
1002   saurabh  [email protected]
1003   nitesh   [email protected]

Another table MessageHistory (Only required columns are shown)

MsgCode From  To
-----------------------------
MSG001  1000  1001,1002,1003
MSG002  1001  1000,1002,1003

I am storing UserIds in From and To columns...

I am trying to create a stored procedure to display the Email History of particular message code

Create Procedure proc_GetMessageHistory
        @MsgCode varchar(50)
        as
        Begin
           Select * From MessageHistory Where MsgCode=@MsgCode 
        End

The result is coming as shown above in MessageHistory table...but I want to show respective UserEmailIDs instead of UserID (e.g. '[email protected]' instead of 1000)...

How could I do this in a stored procedure? How could I use inner join in this case specially with comma-separated values? Please help...thanks

**DO NOT** use comma-separated values - that's **horribly bad design** and always will be a mess and an awful hack to use. You need to use **properly normalized** database tables - then a JOIN will be a no problem at all... — marc_s, Mar 17 '13 at 09:11
Yes i agree..that will be the very final solution for this to change DB structure...but isn't there any way to achieve this without doing that? — Amol Kolekar, Mar 17 '13 at 09:19
There are some god-awful, crazy messy hacks - I'd rather not think about those, though ..... (shudder) — marc_s, Mar 17 '13 at 09:35
You would need to implement the equivalent of string.Split as a UDF, join against the output of that, and then join to UserMaster. You can do it but it would be very slow. — Phil, Mar 17 '13 at 09:38
You can so it like in [this answer](http://stackoverflow.com/questions/14911167/split-function-in-sql-server-2008/14918846#14918846) — Mikael Eriksson, Mar 17 '13 at 10:44

Ian Preston · Answer 1 · 2013-03-17T13:29:33.007

As everyone has already noted, this should never be any sort of permanent solution, as there no way it will ever perform in an efficient manner. Also, that sort of denormalised structure is likely to have any number of issues. That said...

List of email addresses per messages, i.e. one recipient per row:

select m.MsgCode
  , sender = s.EmailID
  , recipient = u.EmailID
from MessageHistory m
  inner join UserMaster s on m.[From] = s.UserID
  inner join UserMaster u on charindex(cast(u.UserID as varchar), m.[To]) > 0

SQL Fiddle with demo.

List of messages, comma separated list of email addresses, one message per row:

with emails as
(
  select m.MsgCode
    , recipient = u.EmailID
  from MessageHistory m
    inner join UserMaster u on charindex(cast(u.UserID as varchar), m.[To]) > 0
)
select m.MsgCode
  , [From] = u.EmailID
  , [To] = stuff
    (
      (
        select ',' + recipient
        from emails e
        where m.MsgCode = e.MsgCode
        for xml path('')
      )
      , 1
      , 1
      , ''
    )
from MessageHistory m
  inner join UserMaster u on m.[From] = u.UserID

SQL Fiddle with demo.

After above comments i have changed the structure of DB ...though i will try to understand and implement this code...its new learning to me...thank you for your great effort...+1 :) — Amol Kolekar, Mar 17 '13 at 14:48

Using Inner Join on Comma-Separated Values in Stored Procedure

1 Answers1