Help with a Stored Procedure......

Question

Help with a Stored Procedure......

Mitchel

SSC Veteran

Points: 298
More actions
February 20, 2006 at 10:52 am

#168457

Hi Again,
Thanks to everyone who helped me with my last question! []
Here is the new one question:
- see attached image or here: http://www.fotash.com/new1/sto_sample2.gif
I have a report that queries the "instructors" table based on their "status" and brings back the results, I then use the "instructors" unique_id to query the "instructors_can_teach" table and I perform a nested repeat so it lists all the classes they can teach. I then proceed to the next instructor that was found and the process repeats.
As you can imagine it takes a while to process, with thousands of instructors and the amount of classes they can teach.
I can picture the Stored Procedure in my head but I can't figure out how to write it. If I could write it in English and not SQL it would go something like this:
-Search the instructor table
-Select the first instructors found and get there ID
-Get the ID of the first instructor returned and use that value to search which courses they can teach and store the courses they can teach into a variable called "classteach"
-Repeat for the next instructor
Any help would be greatly appreciated!
Thanks,
Mitch

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Joe Nakanishi-3 SSC Eights! Points: 828 More actions · Answer 1

I'll answer here too in case you've abandoned your other post...

Do you really need the data in this format as you indicated this was for a report. Typically it's best to handle formatting (class_code, class_code, ...) and display of the data within the report. If you can get away with formatting in your report a simple outer join will give you what you need.

If however you're insistent on the row based solution...

DECLARE @display TABLE

(

InstructorID INT,

ClassesTheyCanTeach VARCHAR(7000)

)

INSERT INTO @display

SELECT InstructorID, '' FROM Instructors ORDER BY InstructorID

DECLARE @RowCnt INT, @ClassID INT, @InstID INT, @Class VARCHAR(255)

SELECT TOP 1 @ClassID = [ID],

@InstID = InstructorID,

@Class = Class_Code

FROM instructors_can_teach

ORDER BY [ID]

SET @RowCnt = @@ROWCOUNT

WHILE @RowCnt <> 0

BEGIN

UPDATE @display SET ClassesTheyCanTeach = CASE LEN(ClassesTheyCanTeach) WHEN 0 THEN @Class ELSE ClassesTheyCanTeach + ', ' + @Class END

WHERE InstructorID = @InstID

SELECT TOP 1 @ClassID = [ID],

@InstID = InstructorID,

@Class = Class_Code

FROM instructors_can_teach

WHERE [ID] > @ClassID

ORDER BY [ID]

SET @RowCnt = @@ROWCOUNT

END

SELECT i.instructorname, d.classestheycanteach

FROM @display d

INNER JOIN instructors i on d.instructorid = i.instructorid

Mitchel SSC Veteran Points: 298 More actions · Answer 2

Hi Joe,

I'm not sure how an outer-join could do what I need to do. The problem stems from the fact that any single instructor can and probably will have multiple entries in the table that stores all of the classes they can teach. I believe the outer join would display the instructor every time it found an entry for it in the table that stores the classes they can teach. Also, please remember that this is not the whole report, there are at least 3 more fields that have to be reported on from different tables all based on the instructors id.

Something like:

John Smith english100

John Smith math100

John Smith science300

Where what I want is:

John Smith english100, math100, science300

I can't see how I could do that with an Inner Join.

Thanks for helping and I will also try your code in a little bit.

Thanks!

Mitch

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Is this comp sci homework?

I'd suggest a cursor (forward_only, read_only) over the instructors and use a select statement for each to build up the list of courses. Something like

SELECT @Classlist=''

SELECT @Classlist = @Classlist + Subject FROM instructors_can_teach WHERE Instructorname = @currentInstructor

You can then insert that into a temp reporting table.

As Joe noted, this is not somethng that SQL's good at and is best left up to the presentation layer (report/application)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Mitchel SSC Veteran Points: 298 More actions · Answer 4

Hi Everyone,

Sorry for the delayed response but I had some personal things to take care of.

I appreciate everyones help with this and the great suggestions.

I ended up doing some research and stubmled upon an awesome tutorial for Data-Shaping and that was the answer to my problem. What a powerfull and useful technique.

Here is a link to the article:

http://www.4guysfromrolla.com/webtech/092599-1.shtml

Thanks!!!

Mitch