Solved

Stored procedure...

Posted on 2009-04-03
9
225 Views
Last Modified: 2013-12-17
Hi
I am a real beginner in SQL.I want to write a stored procedure.
I have two tables,tmsmailinfo and TMSMailDetails
tmsmailinfo  has fields: ID,SenderID,Subject,MailBody
TMSMailDetails has fields: MId,ReceiverId
where MId is ID in tmsmailinfo.

e.g, TMSMailDetails has data
Mid                ReceiverID                       Type
1      xyz@xyz.com      T
1      xyz3@xyz.com      C
1      xy2z@xyz.com      C
1      pqr@pqr.com      B
2      abc@abc.com      T
Here T stands for To,C for CC,B for BCC


tmsmailinfo  has data
Id                  SenderId        Subject          MailBody
1      ts@t.com       TIMS update       Test mail
2      s@gmail.com TIMS update  Test mail two

I want the output as

Id SenderId   Subject           MailBody  ReciverId            CC                                                  Bcc
1 ts@t.com      TIMS update       Test mail  xyz@xyz.com   xyz3@xyz.com,xyz2@xyz.com    pqr@pqr.com


0
Comment
Question by:johny_bravo1
  • 5
  • 3
9 Comments
 
LVL 14

Expert Comment

by:Daniel Junges
ID: 24058306
CREATE PROCEDURE selMailInfo
AS BEGIN
  select i.id, i.SenderId, i.Subject, i.MailBody, d.ReciverId, d.CC, d.Bcc
  from tmsmailinfo i, TMSMailDetails d
  where i.id=d.Mid
END
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24058340
Thanks junges,
but that will not work.


What I am doing is

Create PROCEDURE GetMails
AS
select i.senderid,i.subject,i.mailbody,i.attach1,i.attach2,i.attach3,i.attach4,i.attach5,i.status,
d.receiverID,d.Type from tmsmailinfo i inner join TMSMailDetails d on i.ID=d.MId


What I want is, if the type  is C,then those reciverId should be added into a new column as CC,same for B as BCC
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24058347
I want to add receiverid of type T,C,B in a sepearate column but in a single row for that MId
0
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
LVL 14

Expert Comment

by:Daniel Junges
ID: 24058436
you can do the follow:

select i.senderid,i.subject,i.mailbody,i.attach1,i.attach2,i.attach3,i.attach4,i.attach5,i.status,
d.receiverID,d.Type,
CASE d.Type WHEN 'C' THEN d.receiverID ELSE null END as CC,
CASE d.Type WHEN 'B' THEN d.receiverID ELSE null END as BCC

from tmsmailinfo i inner join TMSMailDetails d on i.ID=d.MId
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24058470
Ya thats's ok.I think I am not able to give you what I want.

Suppose there is one row in tmsMailInfo with id=1,
in TMSMailDetails i may have 4 rows for the id 1,
let's say 1 for T,2 for C and 1 for B

I want the output in a single row for this data
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24058479
That id may confuse u
so
Suppose there is one row in tmsMailInfo with id=345,
in TMSMailDetails i may have 4 rows for the id 345,
let's say 1 for T,2 for C and 1 for B

I want the output in a single row for this data
0
 
LVL 14

Expert Comment

by:Daniel Junges
ID: 24058606

"let's say 1 for T,2 for C and 1 for B"

sorry but i not understand what you mean
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24058622
There is one row of type 'T'
two rows of type 'C'
1 row of type 'B'

0
 
LVL 5

Accepted Solution

by:
kosturdur earned 500 total points
ID: 24059045
Hi johny,
You could use cursors to do that.

By using Cursors;
first collect all Type 'T's and where mid= 1 and set a variable called @T
second collect all Type 'C's and where mid= 1 and set a variable @C
third collect all Type 'B's and where mid= 1 and set a variable @B
then made a select;

select senderid, subject, mailbody,attach1,attach2,attach3,attach4,attach5,status,
@T, @B, @C from tmsmailinfo

The sample of using a cursor is like;(just a sample not the one you need)

***************CODE BEGINS*********************
DECLARE mycursor CURSOR FOR
SELECT     lookup.UserID, users.[User], lookup.EMailID,emails.EMailName
FROM       lookup INNER JOIN
                      emails ON lookup.EMailID = emails.EMailID INNER JOIN
                      users ON lookup.UserID = users.UserID
GROUP BY lookup.UserID, users.[User], lookup.EMailID, emails.EMailName


OPEN mycursor

DECLARE @LastUserID int
DECLARE @CurrentUserID int
DECLARE @CurrentUser varchar(20)
DECLARE @CurrentEMailID int
DECLARE @EMailType varchar(100)
DECLARE @CurrentEMailType varchar(100)
DECLARE @CurrentEMailName varchar(50)

FETCH NEXT FROM mycursor INTO @CurrentUserID, @CurrentUser, @CurrentEMailID, @CurrentEMailName
SET @LastUserID = @CurrentUserID
WHILE (@@FETCH_STATUS <> -1)
BEGIN
    IF (@@FETCH_STATUS <> -2)
    BEGIN
      IF (@LastUserID = @CurrentUserID)
      BEGIN
        SET @EMailType = (CASE WHEN @EMailType is NULL THEN @CurrentEMailName ELSE @EMailType +' ; '+ @CurrentEMailName END)
      END
      ELSE
      BEGIN
        SELECT  @CurrentUserID, @CurrentUser, @EMailType --Selects
        SET @LastUserID = @CurrentUserID
        SET @EMailType = @CurrentEMailName
      END
    END
    FETCH NEXT FROM mycursor INTO @CurrentUserID, @CurrentUser, @CurrentEMailID, @CurrentEMailName
    SELECT @CurrentUserID, @CurrentUser, @EMailType -- Selects
END
CLOSE mycursor
DEALLOCATE mycursor
Go
***********CODE ENDS*********
HTH,
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
The provided anti-forgery token was meant for user "", but the current user is "testUser". 9 50
VS 2017 18 108
Database maintenance 36 108
Using this function 4 42
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question