May 10, 2005 at 8:41 am
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.
May 10, 2005 at 10:07 am
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.
May 10, 2005 at 12:42 pm
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.
May 11, 2005 at 1:54 am
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 @lInputStringLength1 Int ,
@lPosition1 Int ,
@lSplitChar1 Int ,
@lGroupID Int
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