Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 SP
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mafaldachambel
Starting Member

Portugal
1 Posts

Posted - 04/29/2013 :  09:20:36  Show Profile  Reply with Quote
Hello everyone,

I think my question is very basic... but I need your help resolving this problem in my SP...

I need to add some more people to recieve the e-mail, but only the first insert is working in this section:

--
insert INTO GEMailDestinatario (MailId, Tipo, EmailDestinatario)

select @mailid,
'To',
'mafalda@xpto.pt'
union all
select @mailid,
'cc',
'mafalda22222@xpto.pt'


--
is it wrong to try to do union all when i defined @mailid? What is my alternative? Thank you...




Below the complete SP:


--

ALTER PROCEDURE [dbo].[EnviaEmailOb]

@Utilizador int,
@MGTActividadeId int,
@CircuitosId int

WITH RECOMPILE
as
BEGIN

INSERT INTO GEMail ([From],Assunto,CorpoMensagem, UserCria, DataCria, UserEnvia, DataEnvia, AssuntoComposto, Enviado, CategoriaId, SubCategoriaId, ItemApagado, Lido, Draft)

SELECT 'DSI <dsi@xpto.pt>',
'XPTO - (MGT ' + cast(b.TicketId AS varchar) + ') ' + a.Assunto,
'Descrição do pedido: ' + isnull(a.Descricao,'') + 'Utilizador que criou o ticket: ' + d.Nome + ' ( ' + e.login + ' ) ''Validação pela equipa da DSI: ' + isnull(c.Descricao,'') + 'Atentamente, XPTO',
77,
getdate (),
77,
getdate (),
0,
0,
40,
168,
0,
0,
0

from MGTTickets a inner join MGTActividades b on
b.TicketId = a.TicketId
inner join MGTRECActividades c on
c.MGTActividadeID = b.ActividadeID
inner join ARQLogins e on
a.UtilizadorLoginID = e.loginID
inner join ARQEntidadesLogin f on
e.loginID = f.loginID
inner join ARQEntidades d on
d.EntidadeID = f.EntidadeID

where WFActividadeId = '1333'
and a.UtilizadorLoginID = e.loginID
and @MGTActividadeId = ActividadeId


declare @mailid bigint
select @mailid = scope_identity ()


insert INTO GEMailDestinatario (MailId, Tipo, EmailDestinatario)

select @mailid,
'To',
'mafalda@xpto.pt'

union all
select @mailid,
'cc',
'mafalda2222222@xpto.pt'


insert into GWMailboxMails (MailboxID, MailID,PastaID, PastaSistema, Indexado)

select 38,
@mailid,
197,
1,
1


declare @mailboxmailid bigint
select @mailboxmailid = scope_identity ()


insert into GWMailboxMailsPermissoes (MailboxMailID, UG, UGID, [Read], Write, [Delete], [full])

values (@mailboxmailid,
'G',
3,
1,
1,
1,
1)


insert into GEPermissoes (MailID, UserGrupoId, UG, [Read], Write, [Delete], [full], MailboxId, PastaId, PastaSistema)

values (@mailid,
20,
'G',
1,
1,
1,
1,
38,
197,
1)

END



--







Atentamente,
Mafalda Chambel

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 04/29/2013 :  14:27:49  Show Profile  Reply with Quote
quote:
Originally posted by mafaldachambel

Hello everyone,

I think my question is very basic... but I need your help resolving this problem in my SP...

I need to add some more people to recieve the e-mail, but only the first insert is working in this section:

--
insert INTO GEMailDestinatario (MailId, Tipo, EmailDestinatario)

select @mailid,
'To',
'mafalda@xpto.pt'
union all
select @mailid,
'cc',
'mafalda22222@xpto.pt'


--
is it wrong to try to do union all when i defined @mailid? What is my alternative? Thank you...

The code you posted should work just fine in thoery. Assuming that the MailID does not have a unique constraint on it.

What error are you getting?

I don't think this is your problem, but an alternative is to use a VALUES clause:
INSERT 
	GEMailDestinatario (MailId, Tipo, EmailDestinatario) 
VALUES
	(@mailid, 'To', 'mafalda@xpto.pt'),
	(@mailid, 'cc', 'mafalda22222@xpto.pt')

Edited by - Lamprey on 04/29/2013 14:28:36
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000