Passing variables between stored procedures

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)

Passing variables between stored procedures

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-15 : 10:03:30

jmj writes "I have an application where a user can view report cards of a student, a class or a grade. It is made up of lots of queries with setting of variables in between...
ie: 1st query may have all the variables defined- studentid, classid or grade-- Or it may be asked for all the 2nd graders. The 2nd query grabs all the marks (grades) for the chosen students. A 3rd query grabs the teacher information. The 4th query grabs the school information. The 5th query grabs attendance data, etc...
Needless to say it gets pretty slow processing this from the application so I want to do it in stored procedures. But I can't get the parameters to pass between procedures. ie: my 1st stored procedure pulls up all the students names/grade. But I can't derive their marks for the semester in the 2nd stored procedure....
Can you show an example of how I would set this up? I have tried examples of creating output parameters without luck.
What I have done:
1st stored procedure:
CREATE PROC procGetStudent
@SCHOOLID varchar(4) =NULL,
@GRADE varchar(2) =NULL,
@STUDENTID varchar(18)= NULL OUTPUT

AS
SELECT L.CLASSLINK,
STU.STUDENTID,
STU.LASTNAME,
STU.SCHOOLID,
STU.FIRSTNAME,
STU.GRADE,
STU.LLINK
FROM STUDENTS STU INNER JOIN LINKS L ON
STU.STUDENTID = L.STUDENTID
WHERE STU.SCHOOLID=@SCHOOLID
AND L.TeachType='Homeroom'
AND STU.GRADE=@GRADE

ORDER BY L.LINK, STU.LASTNAME, STU.FIRSTNAME
GO

Then in the query analyzer I tried to test it by doing the following:
USE EduDB
GO
DECLARE @STUDENTID varchar(18)
EXEC procGetStudent
@SCHOOLID='0007',
@GRADE='03',
@STUDENTID=@STUDENTID OUTPUT

SELECT STUDENTID, LASTNAME
FROM STUDENTS
WHERE STUDENTID=@STUDENTID

The first part will get me 56 students- the 2nd part gets me 0.
Any ideas about what I'm doing wrong? I'm using SQL2000 with all patches on. I am using ColdFusion as a front end-but basically that is just going to call the stored procedure. the info I described here was all made/tested in sql manager.
Thanks,
j"

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-03-15 : 15:38:09

Your first Stored Procedure is never assiging a value to @StudentID so nothing gets output. Try adding something like the text in red.

CREATE PROC procGetStudent
@SCHOOLID varchar(4) =NULL,
@GRADE varchar(2) =NULL,
@STUDENTID varchar(18)= NULL OUTPUT

AS
SELECT L.CLASSLINK,
@STUDENTID = STU.STUDENTID,
STU.LASTNAME,
STU.SCHOOLID,
STU.FIRSTNAME,
STU.GRADE,
STU.LLINK
FROM STUDENTS STU INNER JOIN LINKS L ON
STU.STUDENTID = L.STUDENTID
WHERE STU.SCHOOLID=@SCHOOLID
AND L.TeachType='Homeroom'
AND STU.GRADE=@GRADE
ORDER BY L.LINK, STU.LASTNAME, STU.FIRSTNAME
GO

------------------------
GENERAL-ly speaking...

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-03-15 : 15:42:42

No, wait a minute... on second look... your first Stored Procedure isn't assigning a value to @StudentID, that part is true, but it looks like it will return multiple records, not a single value. If you're getting multiple records back, then an output parameter won't work. Are you sure these things are in the right order?

What is this part:
SELECT STUDENTID, LASTNAME
FROM STUDENTS
WHERE STUDENTID=@STUDENTID

supposed to be doing?

And overall, back to your original question, shouldn't this all be in one stored procedure instead of trying to pass things around and around?

------------------------
GENERAL-ly speaking...

jmj
Starting Member

7 Posts

Posted - 2002-03-15 : 16:50:02

Well I thought there should be several small stored procedures versus one huge one. I need to have information to fill a report card-there are several queries hitting different tables- from attendance, school info, teacher info, grades for each quarter, comments. They all come together to fill in a pdf report card.

You are right- I do want to get multiple records back- ie the user has the choice to get report cards for the whole class/grade or individual student. Hence the
SELECT STUDENTID, LASTNAME
FROM STUDENTS
WHERE STUDENTID=@STUDENTID

Basically the stored procedure I did was an abbreviated version of what I thought I would end up with. I thought that if I got it going and the variables passed then I could add to it.
Does that make sense.
j

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-03-15 : 19:04:55

J,

Is it true (I believe it is) that all the information on each report card will, in one way or another, be related to the student? If so, then I believe that what would be most efficient is to have one SELECT with multiple table joins rather than a lot of little selects where you then have to later merge the separate pieces of data together. In order to accomplish this, all of the data has to be related to each other, which probably requires some juncture tables to handle the many-to-many relationships inherent in a system like this. All of these should have already been built. Assuming you have this all in place, you would do something like this:

SELECT *
FROM Students S.
JOIN StudentXClasses SXC ON S.StudentID = SXC.StudentID
JOIN Classes C ON SXC.ClassID = C.ClassID
JOIN Grades G ON SXC.StudentID = G.StudentID and SXC.ClassID = G.ClassID
JOIN Teachers T on C.TeacherID = T.TeacherID

Of course, I'm just guessing on table names and data structure here, but this is what I would expect to see. Then you don't have to pass values between multiple sprocs and try to merge it all together later. You can add a WHERE S.StudentID = @StudentID clause if you want to get just one student.

HTH,

Mark

------------------------
GENERAL-ly speaking...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources