bobbellows
asked on
Stored Procedure Selects Wrong Records
I have a very simple data table that stores records of interventions teachers have with their students. I need to select records for a particular student recorded by a particular teacher of up to 3 different intervention types. If I select the record for one Intervention Type the query works perfectly. However, if I want to select the records of more than one type for a student the results are all the students that have the both intervention types. It seems to ignore the student_id filter. Below is the table design, the proc that works when selecting one type the proc that doesn't work when trying to find more than one type.
The DB type is MS SQL 2008
TID is the Teacher's ID
CID is the Campus ID -- not used in query
Column Name DataType Allow Nulls
InterventionRecordNumb int No
InterventionType nvarchar(25) Yes
student_id int No
notes nvarchar(MAX) Yes
Intervention varchar(MAX) No
TID nchar(10) Yes
Incident nvarchar(MAX) Yes
CID varchar(10) Yes
ClassroomStrategy nvarchar(250) Yes
THIS PROCEDURE WORKS
SELECT dbo.InterventionRecordInde x_local. InterventionRecordNumber, dbo.InterventionRecordInde x_local.st udent_id, dbo.InterventionRecordInde x_local.In tervention Type, dbo.InterventionRecordInde x_local.TI D
FROM dbo.InterventionRecordInde x_local
WHERE (dbo.InterventionRecordInd ex_local.s tudent_id = 1234) AND
(dbo.InterventionRecordInd ex_local.T ID = 987) AND
(dbo.InterventionRecordInd ex_local.I nterventio nType = 'Accommodation')
THIS PROCEDURE DOESN'T WORK
SELECT dbo.InterventionRecordInde x_local. InterventionRecordNumber, dbo.InterventionRecordInde x_local.st udent_id, dbo.InterventionRecordInde x_local.In tervention Type, dbo.InterventionRecordInde x_local.TI D
FROM dbo.InterventionRecordInde x_local
WHERE (dbo.InterventionRecordInd ex_local.s tudent_id = 1234) AND
(dbo.InterventionRecordInd ex_local.T ID = 987) AND
(dbo.InterventionRecordInd ex_local.I nterventio nType = N'Accommodation') OR
(dbo.InterventionRecordInd ex_local.I nterventio nType = N'Modification')
I am sure there is some syntax I missed when learning how to write SP's. If you can help me out I would appreciate it. Thanks
The DB type is MS SQL 2008
TID is the Teacher's ID
CID is the Campus ID -- not used in query
Column Name DataType Allow Nulls
InterventionRecordNumb int No
InterventionType nvarchar(25) Yes
student_id int No
notes nvarchar(MAX) Yes
Intervention varchar(MAX) No
TID nchar(10) Yes
Incident nvarchar(MAX) Yes
CID varchar(10) Yes
ClassroomStrategy nvarchar(250) Yes
THIS PROCEDURE WORKS
SELECT dbo.InterventionRecordInde
FROM dbo.InterventionRecordInde
WHERE (dbo.InterventionRecordInd
(dbo.InterventionRecordInd
(dbo.InterventionRecordInd
THIS PROCEDURE DOESN'T WORK
SELECT dbo.InterventionRecordInde
FROM dbo.InterventionRecordInde
WHERE (dbo.InterventionRecordInd
(dbo.InterventionRecordInd
(dbo.InterventionRecordInd
(dbo.InterventionRecordInd
I am sure there is some syntax I missed when learning how to write SP's. If you can help me out I would appreciate it. Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you.
Or
... AND dbo.InterventionRecordInde
Raj