Solved

Stored Procedure Selects Wrong Records

Posted on 2011-03-06
3
326 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

Industry Leaders: 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

Suggested Solutions

Title # Comments Views Activity
reccommendations for a free msft sql query manager? 4 45
Update one rows based on previous row 5 30
Can I skip a node in XML? 9 35
T-SQL: Stored Procedure Syntax 3 34
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

733 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