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