|
|
Posted by jknaty on 05/23/07 14:54
I'm trying to create a function that splits up a column by spaces, and
I thought creating a function that finds the spaces with CHARINDEX and
then SUBSTRING on those values would an approach. I get an error
saying that the I have an Invalid column 'Course_Number'. Not sure
why but I am very new to User Defined Functions. Here is what I have
so far:
CREATE FUNCTION CourseEvalBreakdown
(
@fskey int
)
RETURNS @CourseTable TABLE
(
Col CHAR(2),
Area CHAR(4),
Number CHAR(4),
Section CHAR(4),
Term CHAR(3)
)
AS
BEGIN
DECLARE
@Ind1 tinyint,
@Ind2 tinyint,
@Rows int
DECLARE @crstbl TABLE (FStaffKey int
, Course_Number char(20)
, Term char(3)
, Col char(2)
, Area char(4)
, Number char(4)
, Section char(3)
)
INSERT INTO @crstbl (FStaffKey, Course_Number, Term)
SELECT FStaffKey, Course_Number, Term
FROM Eval
WHERE FStaffKey = @fskey
SET @Rows = @@rowcount
WHILE @Rows > 0
BEGIN
SET @Ind1 = CHARINDEX(' ', Course_Number, 4)
SET @Ind2 = CHARINDEX(' ',Course_Number, (CHARINDEX(' ',
Course_Number, 4)+1))
UPDATE @crstbl
SET Col = SUBSTRING(Course_Number, 1, 2)
WHERE FStaffKey = @fskey
UPDATE @crstbl
SET Area = UPPER(SUBSTRING(Course_Number, 4, @Ind1-4))
WHERE FStaffKey = @fskey
UPDATE @crstbl
SET Number = UPPER(SUBSTRING(Course_Number, @Ind1+1, (@Ind2-
@Ind1)-1))
WHERE FStaffKey = @fskey
UPDATE @crstbl
SET Section = SUBSTRING(Course_Number, @Ind2+1, 3)
WHERE FStaffKey = @fskey
END
INSERT @CourseTable
SELECT Col, Area, Number, Section, Term FROM @crstbl
RETURN
END
GO
Navigation:
[Reply to this message]
|