Solved

Stored Procedure Selects Wrong Records

Posted on 2011-03-06
3
327 Views
Last Modified: 2012-06-22
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.InterventionRecordIndex_local. InterventionRecordNumber, dbo.InterventionRecordIndex_local.student_id, dbo.InterventionRecordIndex_local.InterventionType, dbo.InterventionRecordIndex_local.TID
      FROM         dbo.InterventionRecordIndex_local
      WHERE       (dbo.InterventionRecordIndex_local.student_id = 1234) AND
            (dbo.InterventionRecordIndex_local.TID = 987) AND  
            (dbo.InterventionRecordIndex_local.InterventionType = 'Accommodation')
                         
THIS PROCEDURE DOESN'T WORK

SELECT     dbo.InterventionRecordIndex_local. InterventionRecordNumber, dbo.InterventionRecordIndex_local.student_id, dbo.InterventionRecordIndex_local.InterventionType, dbo.InterventionRecordIndex_local.TID
      FROM         dbo.InterventionRecordIndex_local
      WHERE       (dbo.InterventionRecordIndex_local.student_id = 1234) AND
                            (dbo.InterventionRecordIndex_local.TID = 987) AND  
                            (dbo.InterventionRecordIndex_local.InterventionType = N'Accommodation') OR
                            (dbo.InterventionRecordIndex_local.InterventionType = 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
0
Comment
Question by:bobbellows
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 1

Accepted Solution

by:
bcopping earned 500 total points
ID: 35046137
you need to wrap your or statment in brackets:

SELECT     dbo.InterventionRecordIndex_local. InterventionRecordNumber, dbo.InterventionRecordIndex_local.student_id, dbo.InterventionRecordIndex_local.InterventionType, dbo.InterventionRecordIndex_local.TID
      FROM         dbo.InterventionRecordIndex_local
      WHERE       (dbo.InterventionRecordIndex_local.student_id = 1234) AND
                            (dbo.InterventionRecordIndex_local.TID = 987) AND  
                           ( (dbo.InterventionRecordIndex_local.InterventionType = N'Accommodation') OR
                            (dbo.InterventionRecordIndex_local.InterventionType = N'Modification'))
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 35046164
I've the same correction.

Or

... AND dbo.InterventionRecordIndex_local.InterventionType IN ( N'Accommodation', N'Modification')

Raj
0
 

Author Closing Comment

by:bobbellows
ID: 35046339
Thank you.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question