sqlserver2005中作成数据库邮件配置文件的存储过程

CREATE   PROCEDURE   [ dbo ] . [ CreateMailProfile ]
     @profileName         sysname,
     @profileDescription   nvarchar ( 256 ),
     @accountName         sysname,
     @accountDescription   nvarchar ( 256 ),
     @mailAddress          nvarchar ( 128 ),
     @mailDisplayName      nvarchar ( 128 ),
     @mailServer           nvarchar ( 128 ),
     @mailUsername         nvarchar ( 128 ),
     @mailPassword         nvarchar ( 128 )
AS
BEGIN
     SET  NOCOUNT  ON ;
     BEGIN   TRANSACTION ;     
     DECLARE   @resultStatus   datetime ;
     SELECT   @resultStatus = last_mod_datetime  FROM  msdb.dbo.sysmail_profile
     WHERE   [ name ]   =   @profileName ;
     IF  ( @resultStatus   IS   NOT   NULL )
     BEGIN
         -- 先删除配置文件的用户关联(公用)
         EXECUTE  msdb.dbo.sysmail_delete_principalprofile_sp
             @principal_id   =   0
            , @profile_name   =   @profileName ;
         EXEC  msdb.dbo.sysmail_delete_profile_sp  @profile_name   =   @profileName ; 
     END ;          
     DECLARE   @profileId   int ;
     EXECUTE  msdb.dbo.sysmail_add_profile_sp
         @profile_name   =   @profileName ,
         @description    =   @profileDescription ,
         @profile_id     =   @profileId  OUTPUT;
     SELECT   @profileId ;
     -- 将配置文件变为公用配置
     EXECUTE  msdb.dbo.sysmail_add_principalprofile_sp
         @principal_id    =   0
        , @profile_id   =   @profileId
        , @is_default   =   1 ;
     SELECT   @resultStatus = last_mod_datetime  FROM  msdb.dbo.sysmail_account 
     WHERE   [ name ] = @accountName ;
     IF  ( @resultStatus   IS   NOT   NULL )
     BEGIN
         EXEC  msdb.dbo.sysmail_delete_account_sp  @account_name   =   @accountName ;
     END ;     
     DECLARE   @accountId   int ;
     EXECUTE  msdb.dbo.sysmail_add_account_sp
         @account_name      =   @accountName ,
         @description       =   @accountDescription ,
         @email_address     =   @mailAddress ,
         @display_name      =   @mailDisplayName ,
         @mailserver_name   =   @mailServer ,
         @account_id        =   @accountId  OUTPUT;
     SELECT   @accountId ;
     DECLARE   @maxAccountId   int ;
     SELECT   @maxAccountId   =   ISNULL ( MAX (account_id), 0 )  +   1
     FROM  msdb.dbo.sysmail_profileaccount
     WHERE  profile_id  =   @profileId ;
          
     EXECUTE  msdb.dbo.sysmail_add_profileaccount_sp
         @profile_id   =   @profileId ,
         @account_id   =   @accountId ,
         @sequence_number   =   @maxAccountId ;
     IF  ( @@ERROR   <>   0 )
     BEGIN
         ROLLBACK   TRANSACTION ;
     END
     ELSE
     BEGIN
         COMMIT   TRANSACTION ;
     END ;
END