Dynamic Searching
Posted by Jeff Rix on September 23, 2011 under T-SQL | 
Be the First to Comment
I was recently tasked with providing a query for an application where there are optional drop down filters. The results in each drop down can be populated at any time and the results in the drop down should factor in the values that have already been picked in other drop downs.
For this application building is the only mandatory value. After the building is picked everything else becomes available and dynamic.
The available values for teachers will be determined by the other drop down values that have been picked. The stored procedure below allows each parameter to be optional.
Create procedure [dbo].[spiSearchTeacher]
@building int,
@grade char(2),
@section_key int
–@name varchar(100)
as
begin
set nocount onselect distinct a.staff_id, a.FIRST_NAME+‘ ‘+a.LAST_NAME as Teacher
from Discipline.dbo.Teacher_Class as a join
Discipline.dbo.Student_Class as b on a.section_key = b.Section_Key join
Discipline.dbo.Student_Search as c on b.Student_ID = c.Student_ID
where (a.building = @building or @building is null)
and (a.GRADE = @grade or @grade is null)
and (a.section_key = @section_key or @section_key is null)
@building int,
@grade char(2),
@section_key int
–@name varchar(100)
as
begin
set nocount onselect distinct a.staff_id, a.FIRST_NAME+‘ ‘+a.LAST_NAME as Teacher
from Discipline.dbo.Teacher_Class as a join
Discipline.dbo.Student_Class as b on a.section_key = b.Section_Key join
Discipline.dbo.Student_Search as c on b.Student_ID = c.Student_ID
where (a.building = @building or @building is null)
and (a.GRADE = @grade or @grade is null)
and (a.section_key = @section_key or @section_key is null)
end
