Help with a Stored Procedure......

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help with a Stored Procedure......

Author

Topic

mitchelt
Starting Member

16 Posts

Posted - 2006-02-20 : 12:43:49

Hi Again,

Thanks to everyone who helped me with my last question!

Here is the new one question:
- see attached image or here: [url]http://www.fotash.com/new1/sto_sample2.gif[/url]

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. This is the small report, the other one has 4 nested repeat regions that search other tables.

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

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-02-20 : 12:49:38

Are you looking to get a single result set? What is the output you want? Give an example...AFTER reviewing the sections in Books Online about SELECT statements and JOINs.

mitchelt
Starting Member

16 Posts

Posted - 2006-02-20 : 13:02:52

Hi,

Thanks for looking at my question.

I can handle the joins and selects, the problem is getting repeat values from the table that stores all of the classes an instructor can teach. If they only had only value in the classes they can teach table this would be cake.

Yes...as single result set would work fine.

As far as the output I want...please refer to the sample image (the bottom section). I am looking for the Instructors Name and then a list of all the classes they can teach.

Thanks!

Mitch

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-02-20 : 16:00:03

Of course the question is 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

Non-conformer
Starting Member

14 Posts

Posted - 2006-02-20 : 16:30:09

This question is a classic case of formatting via the back-end database manager versus formatting via some other tool, also known as: use the right tool for the right job.

Given the requirments of this job, I would not have blinked as I whipped out my Crystal Reports and threw together a very simple report (with a SQL query back-end, of course) that would have a "group" by Instructor, with individual lines below it for the individual classes that instructor teaches. And then just for fun, toss in a "count" on the subtotal line to show a count of how many classes that instructor teaches.

Keep it simple.
NC

mitchelt
Starting Member

16 Posts

Posted - 2006-02-20 : 16:48:23

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

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-02-21 : 09:39:42

This is the classic "rows to comma-delimited string" problem that crops up frequently.
Here is some skeleton code that will illustrate the algorithm you need:

declare @DelimitedString varchar(500)
 
Select  @DelimitedString = isnull(@DelimitedString + ', ', '') + FieldValue
From    YourTable
Where   YourCriteria
 
Select @DelimitedString

Note that this method works on only ONE string at a time, so it is best implemented as a user-defined function for inclustion in your SELECT statement.

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-02-21 : 16:53:31

Mitch,

I'm throwing up suggestions based on your posts. I don't beleive you mentioned anything about joining to a third table. Regardless, I'm only offering a suggestion since I don't have all the info.

You can't get a comma delimited string with an outer join, correct you'd get something like:
John Smith english100
John Smith math100
John Smith science300

However you might be able to handle the comma delimited part through your report (Crystal, Reporting Services, etc...). The other reason I ask is the solution I provided is row based which won't ever be as efficient as a set based solution.

You'll have to make the judgement as to what works for you.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-22 : 01:29:18

Refer this
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true

Madhivanan

Failing to plan is Planning to fail

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-02-22 : 09:31:42

Good article, Madhivanan

mitchelt
Starting Member

16 Posts

Posted - 2006-02-22 : 18:23:45

JoeNak: It's 4 tables

Madhivanan: I will read your article and see if it applies.

Thanks everyone for helping.

Mitch

mitchelt
Starting Member

16 Posts

Posted - 2006-03-10 : 16:24:16

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources