SQL Decision statement

Question

I am trying to create a decision statement to determine if the 5 variables (@skill1, @skill2, @skill3, @skill4, @skill5) are not null or empty.

The people_skills table contains different skills for each person. So there may be more than 1 record for a person. The lkp_skills contains the names of those skills.

Based on the 5 different skill fields, I want to ignore the empty string. I also want to search for the strings within the lkp_skills table. If the variable matches the s.skill field of that table then i want it to return that record. If there are multiple skills then I want it to return records that have Both of those skills.

What is the best way of doing this?

DECLARE @zip char(5) = '61265',         -- Zip Code 
        @skill1 varchar(50) = 'sql',  -- Job Skill 1
        @skill2 varchar(50) = 'css',  -- Job Skill 2
        @skill3 varchar(50) = '',  -- Job Skill 3
        @skill4 varchar(50) = '',  -- Job Skill 4
        @skill5 varchar(50) = '',  -- Job Skill 5
        @distance int = 5           -- Distance in miles

SELECT p.people_id, p.first_name, p.last_name, p.address_1, p.address_2, p.city, p.[state],
       p.zip_code, (dbo.sp_getDistance(@zip, zip_code)) AS miles, s.skill
FROM people p
  INNER JOIN people_skills ps ON ps.people_id = p.people_id
  INNER JOIN lkp_skills s ON ps.skill_id = s.skill_id
WHERE (ISNUMERIC(p.zip_code) = 1 AND LEN(RTRIM(LTRIM(p.zip_code))) = 5)
       AND p.ROLE_ID <= 4 
       AND ((dbo.sp_getDistance(@zip, zip_code)) <= @distance)
       AND

       -- DECISION STATEMENT GOES HERE --

ORDER BY miles

Please give more detail for the "and so on for the rest" part. Do you want the first skill variable that has a value? — David, Aug 20 '13 at 19:10

score 3 · Accepted Answer · answered Aug 20 '13 at 19:41

If you can assume that no 2 skills have the same name, then this query will return all people who have all of your skills:

DECLARE @zip char(5), @distance int
DECLARE @Skill1 varchar(50), @Skill2 varchar(50), @Skill3 varchar(50), @Skill4 varchar(50), @Skill5 varchar(50)

SET @zip = '61265'
SET @distance = 5
SET @skill1 = 'sql'
SET @skill2 = 'css'

-- Count how many skills we should search for...
DECLARE @SkillCount int
SET @SkillCount = 0
IF @skill1 IS NOT NULL AND @skill1 != '' SET @SkillCount = @SkillCount + 1
IF @skill2 IS NOT NULL AND @skill2 != '' SET @SkillCount = @SkillCount + 1
IF @skill3 IS NOT NULL AND @skill3 != '' SET @SkillCount = @SkillCount + 1
IF @skill4 IS NOT NULL AND @skill4 != '' SET @SkillCount = @SkillCount + 1
IF @skill5 IS NOT NULL AND @skill5 != '' SET @SkillCount = @SkillCount + 1


SELECT p.people_id, p.first_name, p.last_name, p.address_1, p.address_2, p.city, p.[state],
       p.zip_code, (dbo.sp_getDistance(@zip, zip_code)) AS miles
FROM people p
WHERE (ISNUMERIC(p.zip_code) = 1 AND LEN(RTRIM(LTRIM(p.zip_code))) = 5)
    AND p.ROLE_ID <= 4 
    AND ((dbo.sp_getDistance(@zip, zip_code)) <= @distance)
    AND p.people_id IN (
        -- Get the list of people who have all of the needed skills
        SELECT ps.people_id
        FROM people_skills ps
            INNER JOIN lkp_skills s
            ON ps.skill_id = s.skill_id
        WHERE s.name IN ( @skill1, @skill2, @skill3, @skill4, @skill5 )
        GROUP BY ps.people_id
        -- This is the key.  This filters the list of people to make
        -- sure that the person has ALL of the skills.
        HAVING COUNT(*) = @SkillCount
    )
ORDER BY miles

Also, note that the WHERE clause expressions in your distance search are not guaranteed to run in the order you have specified. For example, if sp_getDistance throws an error if the zip_code is not numeric, then your query may work now, but at some point in the future the query optimizer might change the order of the WHERE clause and you'll start getting an error.

I would have put the skill list in a temp table or table variable, so adding more skills would not require recoding. That would also allow ranking to be included as well (i.e. weighting skills with sum rather than count), etc... — Rawheiser, Aug 20 '13 at 19:56

score 0 · Answer 2 · edited May 23 '17 at 12:05

0

You can use a CASE statement in the WHERE clause or just make the SQL dynamic if it's in a stored procedure (you'd have to handle security, of course).

using CASE in T-SQL in the where clause?

OR

DECLARE @SQL varchar(max);

SET @SQL = 'SELECT ......';

IF LEN(@Skill1) > 0 
BEGIN
    SET @SQL = @SQL + '<your statement>'
END

edited May 23 '17 at 12:05

Community

1
1

answered Aug 20 '13 at 19:20

John

41
6

I don't think `Case` will be enough here since you have this requirement *If there are multiple skills then I want it to return records that have Both of those skills.* The OP will probably need something like `Having count(lkp_skills.ID) = @SkillCount` in there as well – Conrad Frix Aug 20 '13 at 19:25

SQL Decision statement

2 Answers2