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

jknaty wrote:

Please don't multi-post (post to each of several newsgroups separately),
but rather cross-post (post to all of them in a single go). But only do
that if it's truly relevant to all of them.

This is your problem. See microsoft.public.sqlserver for details.

On May 23, 3:22 pm, Ed Murphy <emurph... (AT) socal (DOT) rr.com> wrote:
I understand, amd I'm new to posting to groups and just really wanted
to be sure this got posted to the right group of people to help me
out. I'm still not sure what is wrong but what I did to make things a
bit more clean is break out the part that I really want into a
separate function:

CREATE FUNCTION CourseEvalBreakdown

(

@Course_Number char(20)

)

RETURNS @CourseTable TABLE

(

Course_Number CHAR(20),

Col CHAR(2),

Area CHAR(4),

Number CHAR(4),

Section CHAR(4)

)

AS

BEGIN

DECLARE

@Ind1 tinyint,

@Ind2 tinyint,

@Col char(2),

@Area char(4),

@Number char(4),

@Section char(4)



BEGIN

SET @Ind1 = CHARINDEX(' ', @Course_Number, 4)

SET @Ind2 = CHARINDEX(' ',@Course_Number, (CHARINDEX(' ',
@Course_Number, 4)+1))



SET @Col = SUBSTRING(@Course_Number, 1, 2)

SET @Area = UPPER(SUBSTRING(@Course_Number, 4, @Ind1-4))

SET @Number = UPPER(SUBSTRING(@Course_Number, @Ind1+1, (@Ind2-
@Ind1)-1))

SET @Section = SUBSTRING(@Course_Number, @Ind2+1, 3)



END



INSERT @CourseTable VALUES (UPPER(@Course_Number),@Col, @Area,
@Number, @Section)

RETURN

END

GO

This works and I get the one column that I pass in to the seperated
columns I'm looking for, but now I'm stuck trying to figure out how to
merge those columns into the row that I got the original column which
was passed to this function. I tried to create a select statement
where I call this function but I got stuck there. Then I thought
maybe I can create a function to create a new table with everything
I'm looking for calling this function where I need it and build the
table I'm looking for but again I'm stuck. Any ideas on either?

Thanks,
Jason

jknaty wrote:

What's wrong is that you're thinking in terms of operating on one row
at a time. You came awfully close to using cursors, the usual hallmark
of that particular mistake.

And double-spacing everything. Makes it hard to read.

What you really want is the following:

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 #CourseTableTemp TABLE (
FStaffKey int
, Course_Number char(20)
, Ind1 int
, Ind2 int
, Term char(3)
, Col char(2)
, Area char(4)
, Number char(4)
, Section char(3)
)

INSERT INTO #CourseTableTemp (FStaffKey, Course_Number, Term)
SELECT FStaffKey, Course_Number, Term
FROM Eval
WHERE FStaffKey = @fskey

UPDATE #CourseTableTemp
SET Ind1 = CHARINDEX(' ', Course_Number, 4)

UPDATE #CourseTableTemp
SET Ind2 = CHARINDEX(' ', Course_Number, Ind1 + 1)

UPDATE #CourseTableTemp
SET Col = SUBSTRING(Course_Number, 1, 2)
, Area = UPPER(SUBSTRING(Course_Number, 4, @Ind1-4))
, Number = UPPER(SUBSTRING(Course_Number, @Ind1+1, @Ind2-@Ind1-1))
, Section = SUBSTRING(Course_Number, @Ind2+1, 3)

INSERT INTO @CourseTable
SELECT Col, Area, Number, Section, Term
FROM #CourseTableTemp

RETURN
END
GO

This is great, and looks like what I'm looking for. But I tried it
and I keep getting "Cannot access temporary tables from within a
function". Should I create the table first and then drop it when the
function is done?

jknaty wrote:

Ah, I haven't actually used functions, only stored procedures. You
could either create-and-drop or switch to a SP, I suppose. Or you
could flatten the logic, which would work, but would be ugly and
easy to screw up during maintenance:

insert into @CourseTable (Col, Area, Number, Section, Term)
select
substring(Course_Number,1,2),
upper(substring(Course_Number,4,charindex(' ',Course_Number,4)-4)),
-- etc.

Or you could create a series of smaller UDFs that operate on one
value at a time:

create function CInd1 (@Course_Number char(20))
returns @Ind1 int as
begin
select @Ind1 = charindex(' ',@Course_Number,4)
return
end

create function CInd2 (@Course_Number char(20))
returns @Ind2 int as
begin
declare @Ind1 int
select @Ind1 = CInd1(@Course_Number)
select @Ind2 = charindex(' ',@Course_Number,@Ind1+1)
return
end

create function CCol (@Course_Number char(20))
returns @Col char(2) as
begin
select @Col = substring(@Course_Number,1,2)
return
end

create function CArea (@Course_Number char(20))
returns @Area char(4) as
begin
declare @Ind1 int
select @Ind1 = CourseInd1(@Course_Number)
select @Area = upper(substring(@Course_Number,4,@Ind1-4))
return
end

create function CNumber (@Course_Number char(20))
returns @Number char(4) as
begin
declare @Ind1 int
declare @Ind2 int
select @Ind1 = CourseInd1(@Course_Number)
select @Ind2 = CourseInd2(@Course_Number)
select @Number = upper(
substring(@Course_Number,@Ind1+1,@Ind2-@Ind1-1)
)
return
end

create function CSection (@Course_Number char(20))
returns @Section char(4) as
begin
declare @Ind2 int
select @Ind2 = CourseInd2(@Course_Number)
select @Section = upper(substring(@Course_Number,@Ind2+1,3))
return
end

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
insert into @CourseTable (Col,Area,Number,Section,Term)
select CCol(Course_Number),
CArea(Course_Number),
CNumber(Course_Number),
CSection(Course_Number),
Term
from Eval
where FStaffKey = @fskey
return
end

jknaty (kumpfjn (AT) gmail (DOT) com) writes:
Just change:

To

And use @CourseTableTemp throughout the function.

Logically, temp tables and table variables are very similar, but there
is some fine-print which can be really confusing.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On May 24, 5:39 pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
Sure enough, that worked! Thanks again for everyone's help this is
just what I needed. I do have a SP problem but I'll create a new post
for that.

This is good to know, thanks!