SQL server not returning all rows

Question

Here is the procedure that I am using to search for skills from a single column. There are 3 variables that I need to pass to the SP and I need to get the results accordingly. I do understand that searching for multiple values from a single cell with a delimiter is prone to errors, but this query is not working after I tried to put the whole thing within another IF ELSE condition.

ALTER procedure [dbo].[spFilterThisResume]
@Skill varchar(100),
@Exp INT, @Dt date
AS
DECLARE @NoStart INT
IF (@Exp = '')
    SET @Exp = NULL
IF (@Dt = '')
    SET @NoStart = 1
BEGIN
DECLARE @SkillId varchar(100)
DECLARE MY_CURSOR CURSOR
LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR 
SELECT * FROM dbo.SplitStrings_CTE(@Skill,',')
OPEN MY_CURSOR
FETCH NEXT FROM MY_CURSOR INTO @SkillId
WHILE @@FETCH_STATUS = 0
BEGIN 
    IF (@NoStart = 1)
    BEGIN
        SELECT * FROM tblResume
        (Skills LIKE '%,'+(@SkillId)+',%' OR Skills LIKE (@SkillId)+',%' OR Skills LIKE '%,'+(@SkillId) OR Skills = (@SkillId)
        AND (Experience LIKE @Exp))
    END
    ELSE
    BEGIN
        SELECT * FROM tblResume
        (Skills LIKE '%,'+(@SkillId)+',%' OR Skills LIKE (@SkillId)+',%' OR Skills LIKE '%,'+(@SkillId) OR Skills = (@SkillId)
        AND (Experience LIKE @Exp)
        AND (CreatedDate LIKE @Dt))
    END
END
    PRINT @SkillId
    FETCH NEXT FROM MY_CURSOR INTO @SkillId
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR

The result I got before I tried to put this into IF ELSE block was accurate. It returned even a single occurance of a single skill that I passed as parameter. But I do not understand which part of the query messed up the whole resultset that I got earlier. I unfortunately did not save the working SP into a notepad. So please help me to identify what mistake I made.

NOTE: SELECT * FROM dbo.SplitStrings_CTE(@Skill,',') is a function that I am using to split the input csv into its component arguments

Answer 1

What is going wrong is that your begin and end blocks are all out of line, and you end up in an infinite loop. If I remove actual queries, and add in a comment for each bit to shorten it, and label each begin/end with a number to tie them up you have:

ALTER procedure [dbo].[spFilterThisResume]
AS
-- DECLARE VARIABLES
BEGIN                                       -- 1 BEGIN 1
    -- DECLARE CURSOR AND OPEN IT
    WHILE @@FETCH_STATUS = 0
    BEGIN                                   -- 2 START WHILE
        IF (@NoStart = 1)
        BEGIN                               -- 3 START IF
            -- QUERY WITH NO DATE CHECK
        END                                 -- 3 END IF
        ELSE
        BEGIN                               -- 3 START IF
            -- QUERY WITH DATE CHECK
        END                                 -- 3 END IF
    END                                     -- 2 END WHILE

    PRINT @SkillId
    FETCH NEXT FROM MY_CURSOR INTO @SkillId
END                                         -- 1 END 1

CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR

So your second FETCH NEXT FROM MY_CURSOR INTO @SkillId falls outside of the WHILE. So you never update @@FETCH_STATUS, it will always be 0 after the first fetch, and you will never exit the loop to get to the second FETCH, to correct it you would need to just put your second FETCH inside the loop.

However, this is needless use of a cursor, and as a general rule you should avoid cursors as much as possible. You could just use:

SELECT  *
FROM    tblResume AS r
WHERE   r.Experience LIKE @Exp
AND     EXISTS
        (   SELECT  1
            FROM    dbo.SplitStrings_CTE(@Skill,',') AS s
            WHERE   ',' + r.Skills + ',' LIKE '%,' + s.Value + ',%'
        );

This uses your original split function still, but in a single query, so imagine, as from your previous question you have rows in Resume:

C,C++
P,H,D
ASP,.net,C,C#,C++,R+
C++

And you pass C++,C#, rather than running the query twice, once for C++ and once for C# you can run the query once checking for each value, the exists clause essentially expands out to:

SELECT  
FROM    tblResume
WHERE   r.Experience = @Exp
AND (   ',' + r.Skills + ','  LIKE '%,C++,%'
    OR  ',' + r.Skills + ','  LIKE '%,C#,%'
    )

Also, since @Dt is a DATE, the check @Dt = '' is really checking if `@Dt = '1900-01-01`` which is unlikely to be required behaviour? I suspect you want:

IF @dt IS NULL
BEGIN

    SELECT  *
    FROM    tblResume AS r
    WHERE   r.Experience LIKE @Exp
    AND     EXISTS
            (   SELECT  1
                FROM    dbo.SplitStrings_CTE(@Skill,',') AS s
                WHERE   ',' + r.Skills + ','  LIKE '%,' + s.Value + ',%'
            );
END
ELSE
BEGIN

    SELECT  *
    FROM    tblResume AS r
    WHERE   r.Experience LIKE @Exp
    AND     r.CreatedDate = @Dt
    AND     EXISTS
            (   SELECT  1
                FROM    dbo.SplitStrings_CTE(@Skill,',') AS s
                WHERE   ',' + r.Skills + ','  LIKE '%,' + s.Value + ',%'
            );

END

i.e. Checking where the date is not passed, rather than when it is 1900-01-01.

        ----

ADDENDUM

With regard to why you need to use the predicate as follows:

WHERE ',' + r.Skills + ',' LIKE '%,' + s.Value + ',%'

Again, using some of your previous example data (an one extra row):

1. C
2. ASP,.net,C,C#,C++,R+
3. C++

So if you were just looking for 'C', you might use:

WHERE r.Skills LIKE '%C%';

But as you noted in your previous question, this would also yield row 3 because C++ is like '%C%', which is not correct, it is therefore necessary to look for %,C,%, i.e. where there is a complete match on the skill:

WHERE   r.Skills LIKE '%,C,%';

This would now mean that the first row is not returned, because C on it's own is not like %,C,%' - therefore we need to put the delimeter at the start and end ofr.Skills` to ensure that the first and last terms in the list are not excluded from the search.

If you pass NULL for @Exp you would get no rows returned since the predicate [Anything] = NULL yields NULL, so will never be true. This is not just the case in my answer, it is the case in your question too.

Answer 2

Your sql is returning incorrect results because you are using ( and ) to wrap both AND and OR together.

You need to structure your query like this.

SELECT
 * FROM tblResume
WHERE
(
    Skills LIKE '%,'+(@SkillId)+',%'
    OR Skills LIKE (@SkillId)+',%'
    OR Skills LIKE '%,'+(@SkillId)
    OR Skills = (@SkillId)
)
AND (Experience LIKE @Exp)

Note:As mentioned in the answer by GarethD, you can avoid the use of cursor in such a case.