Posted by Ed Murphy on 05/24/07 19:41

jknaty wrote:

> 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?

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

• Next in forum: Re: Rants. Difficulty to learn ETL tools?
• Prev in forum: Re: Query result sent via e-mail
• Thread view: Re: SUBSTRING in User Defined Function - Invalid column

[Reply to this message]