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

[quoted text, click to view]

I think this is your problem - you're trying to set single variables
based on multiple (one per row) Course_Number values. Instead, add
Ind1 and Ind2 as columns of @crstbl, then do:

update @crstbl
set Ind1 = charindex(' ', Course_Number, 4)

update @crstbl
set Ind2 = charindex(' ', CourseNumber, Ind1+1)