Solved

Stored Procedure Selects Wrong Records

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

707 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now