CREATE PROCEDURE dbo.dscv_add_relationship
(
    @src_class varchar(100),
    @src_hierarchy varchar(100),
    @dst_class varchar(100),
    @dst_hierarchy varchar(100),
    @type varchar(32)
)
AS
DECLARE @src_class_id int,
        @dst_class_id int,
        @src_hierarchy_id int,
        @dst_hierarchy_id int,
        @ROWCOUNT int,
        @emessage  char(255)
BEGIN        
    /* get common db ids */
    SELECT @src_class_id=class_id FROM dbo.ca_class_def WHERE
class_name=@src_class;
    IF (@src_class_id IS NULL)
    BEGIN
        /*set @emessage = 'Source Class is not found in
Database';*/
        /*RAISERROR(@emessage,1,1)*/
        SELECT @src_class_id=-class_id  FROM dbo.tng_class
where name = @src_class;
    END
    
        IF(@src_class_id IS NULL)
    BEGIN
           set @emessage = 'Source Class is not found in
Database';
           RAISERROR(@emessage,1,1)
    END
    
    SELECT @src_hierarchy_id=hierarchy_id FROM dbo.ca_hierarchy
WHERE hierarchy_name=@src_hierarchy;
    IF (@src_hierarchy_id IS NULL)
    BEGIN
        set @emessage = 'Source Hierarchy is not found in
Database';
        RAISERROR(@emessage,2,1)
    END

    SELECT @ROWCOUNT=COUNT(*) FROM dbo.ca_class_hierarchy
    WHERE hierarchy_id=@src_hierarchy_id AND (@src_class_id =
child_class_id OR @src_class_id=parent_class_id);
    IF (@ROWCOUNT <= 0)
    BEGIN
        set @emessage = 'Source Class is not in Source
Hierarchy';
        RAISERROR(@emessage,3,1)
    END

    SELECT @dst_class_id=class_id FROM dbo.ca_class_def WHERE
class_name=@dst_class;
    IF (@dst_class_id IS NULL)
    BEGIN
            SELECT @dst_class_id=-class_id  FROM dbo.tng_class
where name = @dst_class;
        /*set @emessage = 'Destination Class is not in
Database';*/
        /*RAISERROR(@emessage,4,1)*/
    END
        IF (@dst_class_id IS NULL)
        BEGIN
             set @emessage = 'Destination Class is not in Database';
         RAISERROR(@emessage,4,1)
        END
    SELECT @dst_hierarchy_id=hierarchy_id FROM dbo.ca_hierarchy
WHERE hierarchy_name=@dst_hierarchy;
    IF (@dst_hierarchy_id IS NULL)
    BEGIN
        set @emessage = 'Destination Hierarchy is not in
Database';
        RAISERROR(@emessage,5,1)
    END

    SELECT @ROWCOUNT = COUNT(*) FROM dbo.ca_class_hierarchy
    WHERE hierarchy_id=@dst_hierarchy_id AND (@dst_class_id =
child_class_id OR @dst_class_id=parent_class_id);
    IF (@ROWCOUNT <= 0)
    BEGIN
        set @emessage = 'Destination Class is not in
Destination Hierarchy';
        RAISERROR(@emessage,6,1)
    END

    INSERT INTO dbo.ca_dscv_class_relationships (src_class_id,
dst_class_id, src_hierarchy_id, dst_hierarchy_id, type)
    VALUES (@src_class_id, @dst_class_id, @src_hierarchy_id,
@dst_hierarchy_id, @type);
END
GO
GRANT EXECUTE ON  [dbo].[dscv_add_relationship] TO [dscadmin]
GRANT EXECUTE ON  [dbo].[dscv_add_relationship] TO [uniadmin]
GRANT EXECUTE ON  [dbo].[dscv_add_relationship] TO [wvadmin]
GO