if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spS_EmailAttachments]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spS_EmailAttachments]
GO

CREATE procedure [dbo].[spS_EmailAttachments]
	@EmailMessageId int
as
begin
	select a.AttachmentId, a.SourceType, a.SourceId, a.CreatedById, a.FileName, a.AttachDate, a.Description
	from Attachments a
		inner join EmailAttachments ea on a.AttachmentId = ea.AttachmentId
	where ea.EmailMessageId = @EmailMessageId
end

GO

GRANT  EXECUTE  ON [dbo].[spS_EmailAttachments]  TO [public]
GO


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spI_EmailMessages]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spI_EmailMessages]
GO

CREATE PROCEDURE dbo.spI_EmailMessages

@MailAccountId int,
@EmailMessageSender nvarchar(200),
@EmailMessageTo nvarchar(200),
@EmailMessageCC nvarchar(200) = Null,
@EmailMessageBCC nvarchar(200) = Null,
@EmailMessageSubject nvarchar(500),
@EmailBody ntext,
@SentDate datetime,
@ItemId int,
@ItemType int,
@IsIncomingMessage bit

AS

DECLARE @EmailMessageId int

INSERT INTO EmailMessages
(
	MailAccountId,
	EmailMessageSender, 
	EmailMessageTo, 
	EmailMessageCC, 
	EmailMessageBCC, 
	EmailMessageSubject, 
	EmailBody, 
	SentDate, 
	ItemId, 
	ItemType, 
	IsIncomingMessage
)
VALUES
(
	@MailAccountId,
	@EmailMessageSender, 
	@EmailMessageTo, 
	@EmailMessageCC, 
	@EmailMessageBCC, 
	@EmailMessageSubject, 
	@EmailBody, 
	@SentDate, 
	@ItemId, 
	@ItemType, 
	@IsIncomingMessage
)

SET @EmailMessageId = scope_identity()

SELECT @EmailMessageId

GO


GRANT  EXECUTE  ON [dbo].[spI_EmailMessages]  TO [public]
GO