|
|
Posted by Ed Murphy on 05/24/07 15:50
jknaty wrote:
> On May 23, 3:22 pm, Ed Murphy <emurph...@socal.rr.com> wrote:
>> jknaty wrote:
>>> SET @Ind1 = CHARINDEX(' ', Course_Number, 4)
>>> SET @Ind2 = CHARINDEX(' ',Course_Number, (CHARINDEX(' ',
>>> Course_Number, 4)+1))
>> This is your problem. See microsoft.public.sqlserver for details.
>
> 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:
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.
> CREATE FUNCTION CourseEvalBreakdown
>
> (
>
> @Course_Number char(20)
>
> )
And double-spacing everything. Makes it hard to read.
> 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?
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
Navigation:
[Reply to this message]
|