SQL Server 2008 Query with Multiple Values

Question

I have the following Table in my SQL Server 2008 database

dbo.FormEmployment
------------------
employmentID int (PK)
formID int (FK)
gradeID int
employerName nvarchar

I wish to create a query that will select all Users based on their gradeID. At the moment I have the following query

DECLARE @gradeID int
SET @gradeID = 1

SELECT distinct u.userID, u.userTypeID, u.firstName, u.lastName, u.email, u.password, u.contactNumber, u.organisationID, u.emailVerificationCode, u.mobileVerificationCode, u.userStatusID 

FROM [User] u, dbo.FormEmployment emp, dbo.Form f

WHERE u.userTypeID = 47 
--Grades
AND emp.gradeID = @gradeID
AND f.formID = emp.formID
AND f.locumID = u.userID

The above query allows me to state a single gradeID and then get all Users who match that gradeID. However, I also need to be able to state several gradeIDs, for example, I would like for my parameter, @gradeID, to be like this

SET @gradeID = 1, 2, 3

So that it would get all the Users who have grades 1, 2 or 3.

Is there anyway of doing this? Any help or guidance with this would be greatly appreciated as the search I have to build for an application relies on it.

Thanks.

Edit

More information about how the search will work.

Each GradeID will be stored in a table within the database. The User will see a checkboxlist of the gradeIDs, and they will then be able to select as many gradeIDs from this list as they wish. Once they click 'submit', the gradeIDs they have selected will be passed to my stored procedure to perform the search.

CatchingMonkey - Yes, gradeID will always be of type integer, eg, 1,2,3,4,5,6,7...and so on — tcode
– tcode, Commented May 30, 2012 at 9:51
if users can check whatever grades they want, the resulting list won't be a continuous range from 1 to 7 or whatever, but a discrete list, e.g. [1,2,10,15,20,100] — Aprillion
– Aprillion, Commented May 30, 2012 at 10:24

Quassnoi · Accepted Answer · 2012-05-30 09:54:25Z

3

DECLARE @grades
        TABLE
        (
        id INT
        )

INSERT
INTO    @grades
VALUES
        (1),
        (2),
        (3)

SELECT   u.userID, u.userTypeID, u.firstName, u.lastName, u.email, u.password, u.contactNumber, u.organisationID, u.emailVerificationCode, u.mobileVerificationCode, u.userStatusID 
FROM     [User] u
WHERE    userId IN
         (
         SELECT  f.locumId
         FROM    FormEmployment emp
         JOIN    Form f
         ON      f.formId = emp.formId
         WHERE   emp.gradeId IN
                 (
                 SELECT  id
                 FROM    @grades
                 )
         )
         AND u.userTypeID = 47

answered May 30, 2012 at 9:54

Quassnoi

428k94 gold badges628 silver badges623 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

tcode Over a year ago

Quassnoi - Instead of hardcoding the grades (1,2,3) to insert into the grades Table, how could I populate the grades Table with integers from another table, ie, by doing a select? Like this INSERT INTO @grades VALUES(select gradeID from dbo.ShiftGrade where shiftID = 10)

Quassnoi Over a year ago

@tgriffiths: this would work too. In ASP.NET you can pass a table variable as a parameter to a query (or a stored procedure). But why don't you just put select gradeID from dbo.ShiftGrade where shiftID = 10 into the IN clause?

tcode Over a year ago

Quassnoi - I put the 'select gradeID from dbo.ShiftGrade where shiftID = 10' into the 'IN' clause and it worked good, thanks. However, I also tried the option of putting it in like this 'INSERT INTO @grades VALUES(select gradeID from dbo.ShiftGrade where shiftID = 10)' however this does not seem to work? It gives me an error saying 'Subquery returned more than 1 value'. Is there a way of fixing this? Thanks.

Quassnoi Over a year ago

@tgriffiths: INSERT INTO @grades SELECT gradeId FROM shiftGrade WHERE shiftId = 10

tigrou · Accepted Answer · 2012-05-30 09:50:23Z

0

One possiblity :

SELECT (...) 
FROM (...) 
WHERE emp.gradeID IN (@gradeID1, @gradeID2, @gradeID3)

If the number of parameters is not know, you can generate the sql IN clause at runtime (and at the same time add the parameters to the SqlCommand instance)

answered May 30, 2012 at 9:50

tigrou

4,5965 gold badges41 silver badges69 bronze badges

4 Comments

tcode Over a year ago

Thanks, but the main problem with this solution is that there could be as many 100 gradeIDs, therefore, it would mean writing IN (@gradeID1....@gradeID100)...which is something I'd prefer not to do

tigrou Over a year ago

I'm not sure to understand what you need. Are the gradeID know in advance ? If not, where are they defined (in a database table, or given by the application?). In your question you say you want to use the sql query in a application but without giving any information about platform or framework.

tigrou Over a year ago

Ok, I didn't know the sql statement was in a stored procedure. That's a totally different question.

tcode Over a year ago

What difference does it make? The query syntax should be very similar.

Collectives™ on Stack Overflow

SQL Server 2008 Query with Multiple Values

2 Answers 2

4 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related