performing a loop operation in SQL server query

Question

I' writing a search function in C# SQL Server, my users can select multiple job groups and this function should check all of the selected group ids in my job table, how can I perform a loop operation in SQL Server? this is my table general schema:

id int, jobname varchar, jobgroup int.....

I use following query to select my jobs (based on jobgroup):

select * from tblJobs where jobgroup='"+userGroups+"'

this can be true only when userGroups contains one value, but my users can select several group ids, for instance my userGroups can be something like this: 5,7,10,20 (userGroups can contain much more values)

how should I perform a loop operation in my query so that I can have all matching jobs? should I concatenate returned values of several queries each selecting one group ID? I think there are better ways

If you can possibly avoid loops in SQL, do so. The engine is optimized for set operations, not looping, which can really kill performance. — Oded
– Oded, Commented Nov 3, 2012 at 14:23

Oded · Accepted Answer · 2012-11-03 16:12:05Z

1

Don't use looping in SQL - loops tend to kill performance of SQL databases.

In this case, I believe the IN clause can do:

SELECT col1, col2 
FROM tblJobs 
WHERE jobgroup IN ('"+userGroups+"', '"+userGroup2+"')

edited Nov 3, 2012 at 16:12

answered Nov 3, 2012 at 14:25

Oded

501k102 gold badges900 silver badges1k bronze badges

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

4 Comments

Ali_dotNet Over a year ago

thanks, can I dynamically create IN phrase? as I don't know what would be my user request

Oded Over a year ago

@Ali_dotNet - You probably don't need to do so dynamically either. Look at Table Valued Parameters.

Ali_dotNet Over a year ago

thanks Oded but I suppose there is no '=' in this clause, you saved me a lot of time!

Oded Over a year ago

@Ali_dotNet - No, there isn't. That was a typo - sorry.

Collectives™ on Stack Overflow

performing a loop operation in SQL server query

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related