Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Stored Procedure Selects Wrong Records

Posted on 2011-03-06
3
Medium Priority
?
333 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 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

580 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