Solved

Stored procedure...

Posted on 2009-04-03
9
222 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
 
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
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.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

744 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now