USP_ALERT_SEND

Queues an alert to be mailed and saves a copy in .
Parameters

Parameter	Parameter Type	Mode
@MAIL_RECIPIENTS	nvarchar(max)	IN
@MAIL_SUBJECT	nvarchar(255)	IN
@MAIL_BODY	nvarchar(max)	IN
@MAIL_BODYFORMAT	nvarchar(20)	IN
@MAIL_IMPORTANCE	nvarchar(6)	IN
Definition

Copy


CREATE procedure [dbo].[USP_ALERT_SEND] 
    @MAIL_RECIPIENTS nvarchar(max),
    @MAIL_SUBJECT nvarchar(255),
    @MAIL_BODY nvarchar(max),
    @MAIL_BODYFORMAT nvarchar(20), -- ['TEXT', 'HTML']

    @MAIL_IMPORTANCE nvarchar(6) -- ['Low', 'Normal', 'High']


  as 
    declare @INFOMSG nvarchar(4000);
    declare @PROFILENAME nvarchar(128);
    select
        @PROFILENAME = PROFILENAME
    from
        dbo.DATABASEMAILSETTINGS;

    if @PROFILENAME is not null
        begin
            begin try
                exec msdb.dbo.sp_send_dbmail @PROFILENAME, @MAIL_RECIPIENTS, null, null, @MAIL_SUBJECT, @MAIL_BODY, @MAIL_BODYFORMAT, @MAIL_IMPORTANCE;
            end try
            begin catch

                declare @ERRORMESSAGE nvarchar(4000);
                declare @ERRORNUMBER int;
                declare @ERRORSEVERITY int;
                declare @ERRORSTATE int;
                declare @ERRORLINE int;
                declare @ERRORPROCEDURE nvarchar(400) = '';

                -- Assign variables to error-handling functions to capture information for raiserror.

                select
                    @ERRORMESSAGE = error_message(),
                    @ERRORNUMBER = error_number(),
                    @ERRORSEVERITY = error_severity(),
                    @ERRORSTATE = error_state(),
                    @ERRORLINE = error_line();

                if error_procedure() is not null
                    set @ERRORPROCEDURE = rtrim(error_procedure());

                set @INFOMSG='BBWARN: USP_ALERT_SEND: error_message = ' + @ERRORMESSAGE;
                RAISERROR( @INFOMSG, 7  , 1 );

                set @INFOMSG='BBWARN: USP_ALERT_SEND: error_number = ' + CAST(@ERRORNUMBER AS NVARCHAR(10));
                RAISERROR( @INFOMSG, 7 , 2 );

                set @INFOMSG='BBWARN: USP_ALERT_SEND: error_severity = ' + CAST(@ERRORSEVERITY AS NVARCHAR(10));
                RAISERROR( @INFOMSG, 7 , 3 );

                set @INFOMSG='BBWARN: USP_ALERT_SEND: error_state = ' + CAST(@ERRORSTATE AS NVARCHAR(10));
                RAISERROR( @INFOMSG, 7 , 4 );

                set @INFOMSG='BBWARN: USP_ALERT_SEND: error_line = ' + CAST(@ERRORLINE AS NVARCHAR(10));
                RAISERROR( @INFOMSG, 7 , 5 );

                set @INFOMSG='BBWARN: USP_ALERT_SEND: error_procedure = ' + @ERRORPROCEDURE           
                RAISERROR( @INFOMSG, 7 , 6 );

                return 1;           
            end catch
        end
    else
        begin
            set @INFOMSG='BBWARN: USP_ALERT_SEND: error_message = Database mail is not properly configured';
            RAISERROR( @INFOMSG, 7  , 1 );

            return 1;
        end

    return 0;