Reputation: 6095
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,
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?
Any help would be really appreciated.
Upvotes: 0
Views: 49
Reputation: 1340
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)
Upvotes: 2