/****** Object:  Stored Procedure dbo.dscv_add_method    Script Date: 12/1/2005 11:49:14 PM ******/
CREATE PROCEDURE dscv_add_method
(
    @method_name varchar(32),
    @default_method varchar(32),
    @method_type varchar(20),
    @priority int,
    @method_id varchar(32) OUTPUT
)
AS    
DECLARE @default_id varchar(32),
        @tmp_id varchar(32),
        @num int,
        @key varchar(3),
        @len1 int,
        @len2 int,
        @emessage  char(255),
        @tmp_methodid varchar(32)
BEGIN
    /* create method_id @ method name + zero pad number (3 digits) */
    IF (@default_method ='-99')
    BEGIN
        set @default_method=NULL;
    END
    IF (@default_method IS NOT NULL)
    BEGIN
            SELECT @default_id=min(method_id) FROM ca_dscv_class_methods WHERE method_name=@default_method;
    END
    SELECT @tmp_id=max(method_id) FROM ca_dscv_class_methods WHERE method_name=@method_name;
    IF (@tmp_id IS NULL)
    BEGIN
        SELECT @tmp_methodid=left(@method_name,29)+'001';
        SELECT @num=1;
    END
    ELSE
    BEGIN
        /* get length of method_type, find substring, increment number and add for new method_id*/
        SELECT @len1=LEN(@method_name), @len2=LEN(@tmp_id);
        SELECT @num=CAST(RIGHT(@tmp_id,3) as smallint)+1;
        /*zeropad*/
        SELECT @key=RIGHT('000'+ CAST(@num as varchar),3);
        SELECT @tmp_methodid=left(@method_name,29) + @key;
    END

    /* insert method */
    INSERT INTO ca_dscv_class_methods (method_id, method_name, default_method_id, method_type, priority)
    VALUES (@tmp_methodid, @method_name, @default_id, @method_type, @priority);
    SET @method_id = @tmp_methodid
    return @num
END
GO
GRANT EXECUTE ON  [dbo].[dscv_add_method] TO [dscadmin]
GRANT EXECUTE ON  [dbo].[dscv_add_method] TO [uniadmin]
GRANT EXECUTE ON  [dbo].[dscv_add_method] TO [wvadmin]
GO