Posted by jknaty on 05/24/07 17:11

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?

> 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

• Next in forum: Re: Rants. Difficulty to learn ETL tools?
• Prev in forum: Re: Synchronous Bulk-Copy into two table
• Thread view: Re: SUBSTRING in User Defined Function - Invalid column

[Reply to this message]