Solved

Stored procedure...

Posted on 2009-04-03
9
226 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

707 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