grouping and storing the data

  • I need the help in designing and coding of the following requirement.

    I have a master college aaaaaaa and I want to group college a1,a1,a3,a4,a5 into a group 2 and should associate this group to the college aaaaaaa.

    I have a table for college and a table for group

    I am storing the information in the group table as follows.

    Group_id    college_id 

    2  a1

    2  a2

    2  a3

    2   a4

    2   a5

    The users will select 1 to n number of colleges and group it into a single group. So how can I write my stored procedure.

    And also correct me if I am doing anything wrong with storing the group information.

    Thanks.

  • I would do it using a college table

    * CollegeId,

    * CollegeDescription

    I would either have a separate table to maintain the Parent/Child college relationship

    * ParentCollegeId

    * CollegeId

    or I would put the ParentCollegeId in the college table itself.

    CollegeId zero would be "All colleges" with a parent of itself.

    This approach would provide an extensible approach to allow for master colleges with colleges with campuses etc.

    Stored procedures are your standard, "chase up the tree" type stored procedures.

  • I am sorry for being not so clear with my requirement.

    Let me explain you in short and brief.

    I am grouping some of the colleges and assing them to group2 as follows.

    I am grouping colleges a1,a2,a3 and a4 and assiging them to group2.

    So I can write a stored procedure as follows.

    CREATE PROCEDURE sprt_college_group

    @group_id int,

    @college1 int,

    @college2 int,

    @college3 int,

    @college4 int

    AS

    INSERT INTO college_group(group_id,college_id)

    VALUES(@group_id,@college1)

    INSERT INTO college_group(group_id,college_id)

    VALUES(@group_id,@college2)

    INSERT INTO college_group(group_id,college_id)

    VALUES(@group_id,@college3)

    INSERT INTO college_group(group_id,college_id)

    VALUES(@group_id,@college4)

    GO

    So this what I thought to implement in a case where only 4 colleges can be grouped. But My problem is that I dunno the number colleges that are going to be assigned for a group. It could be 1 or 10 or 20 colleges.

    So how can I implemet this in my Stored Procedure.

  • Pass your colleges in as a delimited string and use the following function

    CREATE FUNCTION dbo.fnSplitDelimited ( @sInputString1 VARCHAR(8000) , @sSplitChar CHAR(1))

    RETURNS @tbl_List TABLE (Id1 Int PRIMARY KEY )

    AS

    BEGIN

    DECLARE@lInputStringLength1Int ,

    @lPosition1Int ,

    @lSplitChar1Int ,

    @lGroupIDInt

    SET@lInputStringLength1 = LEN ( @sInputString1 )

    SET @lPosition1=1

    SET@lSplitChar1=1

    WHILE @lPosition1 <= @lInputStringLength1

    BEGIN

    SET @lSplitChar1 = CHARINDEX ( @sSplitChar , @sInputString1 , @lPosition1)

    IF @lSplitChar1 = 0

    BEGIN

    SELECT@lGroupID = CAST( SUBSTRING( @sInputString1 , @lPosition1 ,1+ @lInputStringLength1 - @lPosition1) AS Int )

    SET @lPosition1= @lInputStringLength1 + 1

    END

    ELSE

    BEGIN

    SELECT@lGroupID = CAST ( SUBSTRING( @sInputString1 , @lPosition1 , @lSplitChar1 - @lPosition1) AS INT )

    SET @lPosition1 = @lSplitChar1+1

    END

    INSERT @tbl_List( Id1)

    VALUES( @lGroupID)

    END

    RETURN

    END

    If you do SELECT * from dbo.fnSplitDelimited ('a1,a2,a3' , ',') then you will get a recordset returned as

    a1

    a2

    a3

    So your insert statement would become

    CREATE PROCEDURE sprt_college_group

    @group_id int,

    @CollegeIdList VARCHAR(8000) AS

    SET NOCOUNT ON

    INSERT college_group (group_id,college_id)

    SELECT @group_Id,Id1

    FROM dbo.fnSplitDelimited(@CollegeIdList , ',')

    GO

    Remember to use SET NOCOUNT ON as this will give a slight performance boost.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply