StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow to search on pairs of values in this SQL stored procedure
text
Body
copied!<p>I have the following SQL procedure/select statement that I am working on modifying. I derived this from a previous question I had which can be referenced here: <a href="https://stackoverflow.com/questions/4226342/need-help-with-sql-query">Need help with SQL query</a></p> <p>This works pretty good when I do not have the conditional in there checking for years of experience, but I actually want to modify this so that if I have a list passed in <code>@csvList</code> and another list of <code>@csvYears</code>, then I can pair the values - that is, I can search for <code>Skill1</code> with 5 years of experience, and <code>Skill2</code> with 3 years of experience.</p> <p>Can I just do another JOIN on the table of <code>csvYears</code>? (The <code>SplitCSVStrings</code> function returns a table, and splits the CSV list of values)</p> <p>So behold the existing code:</p> <pre><code> ALTER PROCEDURE [dbo].[EmployeeQuerySkill] @csvList varchar(400), @years int, @fudge int, @hitAll int = 0 AS BEGIN SELECT last_name, id, name, SUM(Experience ) AS 'YearsExperience' FROM ( SELECT e.last_name, e.id, s.name, CASE WHEN r.end_date ='01/01/1901' THEN MAX(datediff(YY,r.start_date, GETDATE())) ELSE MAX(datediff(YY,r.start_date, r.end_date)) END AS 'Experience' FROM employee as e INNER JOIN project AS p ON e.id = p.employee_id INNER JOIN role AS r ON p.id = r.project_id INNER JOIN role_skill AS rs ON rs.role_id = r.id INNER JOIN skill AS s ON s.id = rs.skill_id JOIN SplitCSVStrings(@csvList) AS CSV ON CSV.val = s.name GROUP BY e.last_name, e.id, s.name, r.end_date ) table1 WHERE table1.Experience >= @years GROUP BY last_name, id, name END END </code></pre> <p>How can I modify this to allow me to search for a different condition for each of the skills? <code>@csvList</code> is coming in like "C++, Java" and I want <code>@years</code> to come in as <code>"5, 3"</code> and the corresponding search to show me people that have 5 years of C++ experience, and 3 years of Java experience.</p> <p>Thanks!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload