And just for performance, I substituted table variables for the temp tables (and renamed the variables to fit my team's conventions) ...

USE [master]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[procGetDbMailInformation]

(@intRowCount INT = NULL OUTPUT)

AS

DECLARE @intError INT

DECLARE @tblTemp01 TABLE

(

ProfileId INT,

MailName NVARCHAR(50),

MailDescription NVARCHAR(256)

)

INSERT INTO @tblTemp01

EXECUTE msdb.dbo.sysmail_help_profile_sp ;

DECLARE @tblTemp02 TABLE

(

ProfileId INT,

ProfileName NVARCHAR(50),

AccountId INT,

AccountName NVARCHAR(50),

Sequence INT

)

INSERT INTO @tblTemp02

EXECUTE msdb.dbo.sysmail_help_profileaccount_sp ;

DECLARE @tblTemp03 TABLE

(

AccountId INT,

MailName NVARCHAR(50),

MailDescription NVARCHAR(50),

EmailAddress NVARCHAR(50),

DisplayName NVARCHAR(50),

ReplyToAddress NVARCHAR(50),

ServerType NVARCHAR(50),

ServerName NVARCHAR(50),

Port INT,

UserName NVARCHAR(50),

UseDefaultCredentials NVARCHAR(50),

EnableSsl int

)

INSERT INTO @tblTemp03

EXECUTE msdb.dbo.sysmail_help_account_sp ;

SELECT a.MailName,

b.AccountName,

c.MailDescription,

c.EmailAddress,

c.DisplayName,

c.ReplyToAddress,

c.ServerType,

c.ServerName,

c.Port,

c.UserName,

c.UseDefaultCredentials,

c.EnableSsl

FROM @tblTemp01 AS a

INNER JOIN @tblTemp02 AS b ON a.ProfileId = b.ProfileId

INNER JOIN @tblTemp03 AS c ON b.AccountId = c.AccountId

SELECT @intError = @@Error, @intRowCount = @@RowCount

PRINT 'Row Count: ' + CONVERT(NVARCHAR, @intRowCount)

PRINT 'Error: ' + CONVERT(NVARCHAR, @intError)

RETURN @intError