Posted by Jeff Rix on September 23, 2011 under T-SQL | 
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)
end
Posted by Jeff Rix on September 13, 2011 under T-SQL |
Sometimes data that is necessary for the application is being stored in another database on another server. Executing a remote query, using functions, and joining to many table slows down performance.
My solution to this challenge was to execute a stored procedure nightly that inserts data from another data source (student data) as well as summarized data for each student. The stored procedure spi_LoadStudentSearch brings over some basic student data and uses local scalar-valued functions to get summarized data from the application.
When bringing in data from a different server/database insert the data into a temp table.
ALTER PROCEDURE [dbo].[spi_LoadStudentSearch]
– Add the parameters for the stored procedure here
AS
BEGIN
– SET NOCOUNT ON added to prevent extra result sets from
– interfering with SELECT statements.
SET NOCOUNT ON;–temp table for data in another server
IF OBJECT_ID(‘tempdb..#Results’) IS NOT NULL
DROP TABLE #ResultsCREATE TABLE #Results
(Student_ID INT primary key,
GRADE CHAR(2),
BUILDING INT,
FIRST_NAME VARCHAR(30),
LAST_NAME VARCHAR(30))INSERT INTO #Results
SELECT CAST(STUDENT_ID AS INT) AS STUDENT_ID, GRADE, BUILDING, FIRST_NAME, LAST_NAME
FROM LinkedServer.Yes_Live.dbo.reg
WHERE CURRENT_STATUS =‘A’
–truncate student_search
truncate table Student_Search
DECLARE @CURRENTYEAR INT
SET @CURRENTYEAR = (SELECT top 1 schoolDist.SCHOOL_YEAR
FROM LinkedServer.yes_live.dbo.REG_DISTRICT schoolDist
WHERE schoolDist.DISTRICT = 101845)
insert into Student_Search
select distinct x.student_id, x.grade,x.building, x.first_name, x.last_name, ISNULL(y.Respect_Marks_Count, 0) as Respect_Marks_Count,
ISNULL(y.Responsibility_Marks_Count,0) AS Responsibility_Marks_Count,
ISNULL(y.Respect_Level, 0) AS Respect_Level, ISNULL(y.Responsiblity_Level,0) AS Responsibility_Level
from #Results as x left join
(
select a.student_id, dbo.RespectMarksCount(a.STUDENT_ID) as Respect_Marks_Count,
dbo.ResponsibilityMarksCount(a.STUDENT_ID) as Responsibility_Marks_Count,
dbo.RespectLevel(a.STUDENT_ID) as Respect_Level, dbo.ResponsibilityLevel(a.STUDENT_ID) as Responsiblity_Level
from RISE_MARKS as a
where a.SCHOOL_YEAR = @CURRENTYEAR
–@CURRENTYEAR
) as y
on x.student_id = y.STUDENT_ID
END
This stored procedure executes each night and is scheduled using the SQL Server Agent.
