Solved

Stored procedure...

Posted on 2009-04-03
9
224 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
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.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

839 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