Solved

Stored Procedure Selects Wrong Records

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
date diff with Fiscal Calendar 4 55
TSQL query to generate xml 4 46
error in my cursor 5 41
What can the "OPTION" in the GROUP help me in my query? 4 24
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. …
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 a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

828 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