SQL Server Forums
Profile | Register | 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
 New Topic  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

4612 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  
 New 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.08 seconds. Powered By: Snitz Forums 2000