Add agent parameter sql server

Add agent parameter sql server

GO

-- Add a row into the "MSagent_parameters" table
ALTER procedure [sys].[sp_add_agent_parameter] (
    @profile_id int,
    @parameter_name         sysname,
    @parameter_value        nvarchar(255)
)
as
    declare @slash_parameter_name sysname
    declare @dash_parameter_name sysname
    declare @retcode int
    
    set nocount on

    /*
    ** Security Check: require sysadmin
    */
    IF (ISNULL(IS_SRVROLEMEMBER('sysadmin'),0) = 0)
    BEGIN
        RAISERROR(21089,16,-1) 
        RETURN (1)
    END

    select @parameter_name = RTRIM(@parameter_name)

    -- Append leading '-' when not given
    if (substring(@parameter_name, 1, 1) <> '/' and 
        substring(@parameter_name, 1, 1) <> '-')
    BEGIN
        select @parameter_name = N'-' + @parameter_name
    END

    -- Call proc to validate parameter value
    exec @retcode = sys.sp_MSvalidate_agent_parameter 
                        @profile_id = @profile_id, 
                        @parameter_name = @parameter_name,
                        @parameter_value = @parameter_value
    if @retcode <> 0
        RETURN(1)


    select @slash_parameter_name = lower(stuff(@parameter_name, 1, 1, N'/') collate SQL_Latin1_General_CP1_CS_AS)
    select @dash_parameter_name = lower(stuff(@parameter_name, 1, 1, N'-') collate SQL_Latin1_General_CP1_CS_AS)
     

    /* A parameter may be defined only once per profile */
    if exists ( select * from msdb..MSagent_parameters 
            where profile_id = @profile_id
            and (lower(parameter_name collate SQL_Latin1_General_CP1_CS_AS) = @slash_parameter_name collate SQL_Latin1_General_CP1_CS_AS  
            or lower(parameter_name collate SQL_Latin1_General_CP1_CS_AS) = @dash_parameter_name collate SQL_Latin1_General_CP1_CS_AS))
    BEGIN
        RAISERROR (20067, 16, -1, @parameter_name)  -- The parameter name ''%s'' already exists for the specified profile
        RETURN (1)
    END

    insert into msdb..MSagent_parameters
    values (@profile_id, @parameter_name, @parameter_value) ;

    if @@error <> 0
        return(1)

2020-01-31