SQL query for complex filtering

Question

I have SQL schema something like this.

select Id, AccountId, GradeLevelStart, GradeLevelEnd from AccountProfile

Which contains data as shown in this snapshot.

Here GradeLevelStart, and GradeLevelEnd is Id of Gradelevels. see below query and it's data.

select Id, Name, Abbreviation from GradeLevel

I have created one filter which works using between operator. Flow is something like this,

• user choose a grade 1st, 2nd whatever and this query will return all the accounts which comes in between for that grade and it's GradeLevelStart, GradeLevelEnd.

Here is the query for it.

DECLARE @Grades NVARCHAR(100)
SET @Grades = '3'

    select AccountId, GradeLevelStart, GradeLevelEnd, glStart.Name, glStart.Abbreviation, glEnd.Name, glEnd.Abbreviation 
    from AccountProfile 
        left join GradeLevel glStart on glStart.ID = GradeLevelStart
        left join GradeLevel glEnd on glEnd.ID = GradeLevelEnd
    where @Grades IS NULL OR @Grades BETWEEN GradeLevelStart and GradeLevelEnd

What I am looking for?

This all works fine till now, but it's not yet taking multiple @Grades for filtering.

So what I want is User gives input in a format like below query and system should return all the accounts which has GradeLevelStart and GradeLevelEnd in between this @Grades

SET @Grades = '3,13,'

What I tried so far?

• I have created temp table, and tried to use join but no luck since I don't know how to use between operator there.
• I have also used STRING_SPLIT, but it's of no use.

Any help would be really appreciated.

Answer 1

Use String Split Function to achieve this:

DECLARE @Grades NVARCHAR(100)
SET @Grades = '3,13'

select AccountId, GradeLevelStart, GradeLevelEnd, glStart.Name, glStart.Abbreviation, 
glEnd.Name, glEnd.Abbreviation 
from AccountProfile 
left join GradeLevel glStart on glStart.ID = GradeLevelStart
left join GradeLevel glEnd on glEnd.ID = GradeLevelEnd
OUTER APPLY STRING_SPLIT(@Grades,',') s
where @Grades IS NULL OR (s.value) BETWEEN GradeLevelStart and GradeLevelEnd

Also, You can achieve the Same by using XML Nodes Instead of STRING SPLIT:

For your ref:

declare @S varchar(100) = 'Hello John Smith'

select 
n.r.value('.', 'varchar(50)')
from (select cast('<r>'+replace(@S, ' ', '</r><r>')+'</r>' as xml)) as s(XMLCol)
cross apply s.XMLCol.nodes('r') as n(r)