split function improvision - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

split function improvision

Author

Topic

nellyihu
Starting Member

8 Posts

Posted - 2005-06-10 : 05:40:10

check this script out pls it is an improvision if what is achievable by the split function to split text delimited by a comma.
it is supposed to receive among others two large strings delimited by a comma. ie the @Fsemester and @Ssemester.
feel free to look through and tell me your own opinion

the script:

CREATE PROCEDURE STP_STUDENTCOURSEREGISTERED_INSERT
(
@EntryReg varchar(50),
@CourseMajorCode varchar(7),
@StudentLevel varchar(100),
@Semester int,
@SessionId int,
@Fsemester varchar(8000), --servers as course code for 1st semester
@Ssemester varchar(8000), --servers as course code for 2nd semester
@SplitOn varchar(5)= ","
)

AS
DECLARE @StudentId int
DECLARE @x int
IF EXISTS (SELECT AdmissionId FROM STUDENT_ADMISSIONLIST WHERE EntryRegNo=@EntryReg)
BEGIN
SET @StudentId=(SELECT AdmissionId FROM STUDENT_ADMISSIONLIST WHERE EntryRegNo=@EntryReg)
WHILE (Charindex(@SplitOn,@Fsemester)>0) --insert for first semester first
BEGIN
INSERT INTO STUDENT_COURSE_RegisteredCourses (StudentId,SessionId,Semester,CourseCode,CourseMajorCode)
VALUES (@StudentId,@SessionId,@Semester,@Fsemester,@CourseMajorCode)
SET @x=@x+@@ERROR --check if each insert returns an error > 1
END
WHILE (Charindex(@SplitOn,@Ssemester)>0) --insert for first semester first
BEGIN
INSERT INTO STUDENT_COURSE_RegisteredCourses (StudentId,SessionId,Semester,CourseCode,CourseMajorCode)
VALUES (@StudentId,@SessionId,@Semester,@Ssemester,@CourseMajorCode)
SET @x=@x+@@ERROR --check if each insert returns an error > 1
END
END
ELSE
BEGIN
SET @x=1 --the student doesn't exist in the list
END

RETURN @x

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

nellysoft

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-06-10 : 05:56:08

so what's wrong with this code? udf is probably more suitable for this.


CREATE FUNCTION dbo.Split
(
	@RowData nvarchar(2000),
	@SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(
	Id int identity(1,1),
	Data nvarchar(100)
) 
AS  
BEGIN 
	Declare @Cnt int
	Set @Cnt = 1

	While (Charindex ( @SplitOn,@RowData)>0)
	Begin
		Insert Into @RtnValue (data)
		Select 
			Data = ltrim(rtrim(Substring( @RowData,1,Charindex ( @SplitOn,@RowData)-1)))

		Set @RowData = Substring( @RowData,Charindex ( @SplitOn,@RowData)+1,len( @RowData))
		Set @Cnt = @Cnt + 1
	End

	Insert Into @RtnValue (data)
	Select Data = ltrim(rtrim(@RowData))

	Return
END

Go with the flow & have fun! Else fight the flow

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-10 : 06:30:21

Refer this also
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources