September 24, 2003 at 2:54 pm
Hello,
I have a Stored Procedure set up to take a single parameter which represents a numeric identification for a school, and return student ID numbers with the Student's name from the particualar school. The default for the parameter, '@SchoolNum' is 000.
The following is the code for the current procedure:
***************************
CREATE PROCEDURE InstrumentStudentAssign_sp
@SchoolNum INT = Null
AS
--This is the select list for the combo box in the subform of Music Inventory.
IF (@SchoolNum is null)
BEGIN
SELECT @SchoolNum = 000
END
SELECT
PERMNUM,
LASTNAME + ', ' + FIRSTNAME AS Student_Name, LASTNAME,
FIRSTNAME, SCHOOLNUM, STATUS
FROM Student_Data_Main
WHERE
STATUS IS NULL
And
SchoolNum =@SchoolNum
ORDER BY Lastname, Firstname
****************************
I need to expand on this by setting up additional criteria based on the parameter value that comes into the procedure. I need to execute the above SELECT if the @SchoolNum values are as follows:
If @SchoolNum = 280
or @SchoolNum = 375
or @SchoolNum = 369
or @SchoolNum = 378
IF the @SchoolNum value is either of the following values:
@SchoolNum = 190
or @SchoolNum = 366
or @SchoolNum = 367
or @SchoolNum = 368
or @SchoolNum = 370
or @SchoolNum = 371
or @SchoolNum = 372
Then I need to Execute an alteration of the SELECT statement that looks like this:
SELECT
PERMNUM,
LASTNAME + ', ' + FIRSTNAME AS Student_Name, LASTNAME,
FIRSTNAME, SCHOOLNUM, STATUS
FROM Student_Data_Main
WHERE
STATUS IS NULL
And
(GRADE = 04
or
GRADE = 05)
SchoolNum = @SchoolNum
ORDER BY Lastname, Firstname
The differece in the two SELECT lists occurs where one requires GRADE = 04 or 05, and the other does not.
I have tried a couple of things, and I think I'm almost there, but I'm getting syntax errors.
How can I incorporate the conditional SELECT statements and keep the default of 000?
Thanks again for your help!
CSDunn
September 24, 2003 at 3:47 pm
I STRONGLY suggest you not hardcode the lists of SchoolNums into a stored procedure. If you cannot make this information an attribute of another existing (Schools?) table, then create your own list as a table:
CREATE TABLE Schools4or5(
SchoolNum smallint PRIMARY KEY)
insert schools4or5
select 190
union select 366
union select 367
union select 368
union select 370
union select 371
union select 372
Then use a join in your stored procedure:
CREATE PROC InstrumentStudentAssign_sp @SchoolNum smallint = 0
AS
SET NOCOUNT ON
SELECT PermNum, LastName + ', ' + FirstName AS Student_Name, LastName, FirstName, m.Schoolnum, Status
FROM Student_Data_Main m LEFT JOIN Schools4or5 s ON m.Schoolnum = s.schoolnum
WHERE Status IS NULL
AND (Grade IN (4,5) OR s.SchoolNum IS NULL)
AND m.SchoolNum = @SchoolNum
ORDER BY Lastname, Firstname
--Jonathan
--Jonathan
September 24, 2003 at 4:28 pm
Thanks for your help. I have a table that already contains the unique school numbers. The table is called 'tblLocation'.
I edited the procedure as follows:
*****************************************
ALTER PROC InstrumentStudentAssign2_sp
@SchoolNum smallint = 000
AS
SET NOCOUNT ON
SELECT
SD.PermNum,
SD.LastName + ', ' + SD.FirstName AS Student_Name,
SD.LastName AS SD_LastName,
SD.FirstName AS SD_FirstName,
SD.Schoolnum,
SD.Status,
SD.Grade
FROM Student_Data_Main SD
LEFT JOIN tblLocation LC ON SD.Schoolnum = LC.Location2
WHERE SD.Status IS NULL
AND
(SD.Grade IN (04,05)
OR LC.Location2 IS NULL)
AND SD.SchoolNum = @SchoolNum
ORDER BY SD.Lastname, SD.Firstname
******************************************
This works where the @Schoolnum value represents an Elementary school (grades K-5), but does not return any records if the @Schoolnum represents a Middle School (grades 6-8) or a High School (grades 9-12). Grades K-12 in the Student_Data_Main table are expressed as 00,01,02,03,04,05,06,07,08,09,10,11,12.
So I need only the students in grades 04 or 05 when the @Schoolnum is one of the Elementary school numbers. In following with what you presented, if the @Schoolnum value represents a school other than an Elementary school, then all of the students at that school need to show up.
Thanks again!
CSDunn
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply