Stored Proc Optimization Help

CREATE PROCEDURE dbo.spStudentListReport  

 @session_guid  char(120)  

                

AS  

  

-- Variables --  

DECLARE @locations            nvarchar(4000),   

                  @businessunits     nvarchar(4000),   

                  @courses              nvarchar(4000),  

                 @jobtitles              nvarchar(4000),   

                 @roles                  nvarchar(4000),   

                 @status                 char(1),   

                @startdate            datetime,  

                 @enddate             datetime  

   

  

-- Set Locations --  

SET @locations  = (SELECT Field_id FROM tmp_ReportParams WHERE Session_guid = @session_guid AND Field_type='locations')  

IF @locations = ' ' SET @locations = ' '  

  

-- Create Temporary Local Table for Locations --  

DECLARE @locTable TABLE (location_id char(6))  

INSERT INTO @locTable SELECT value FROM dbo.fnc_split(@locations, ',')  

  

  

-- Set Business Units --  

SET @businessunits  = (SELECT Field_id FROM tmp_ReportParams WHERE Session_guid = @session_guid AND Field_type='businessunits')  

IF @businessunits = ' ' SET @businessunits = ' '  

  

-- Create Temporary Local Table for Business Units --  

DECLARE @busTable TABLE (region char(3))  

INSERT INTO @busTable SELECT value FROM dbo.fnc_split(@businessunits, ',')  

  

  

-- Set Job Titles --  

SET @jobtitles = (SELECT Field_id FROM tmp_ReportParams WHERE Session_guid = @session_guid AND Field_type='jobtitles')  

IF @jobtitles = ' ' SET @jobtitles = ' '  

  

-- Create Temporary Local Table for Job Titles --  

DECLARE @jobTable TABLE (code char(12))  

INSERT INTO @jobTable SELECT value FROM dbo.fnc_split(@jobtitles, ',')  

  

  

-- Set Status --  

SET  @status =  (SELECT Field_value FROM tmp_ReportParams WHERE Session_guid = @session_guid AND Field_type='status')  

IF @status = ' ' SET @status = '  '  

  

-- Set Roles --  

SET @roles = (SELECT Field_id FROM tmp_ReportParams WHERE session_guid =  @session_guid AND Field_type='roles')  

  

-- Create Temporary Local Table for Roles --  

DECLARE @rolTable TABLE (role_id char(12))  

INSERT INTO @rolTable SELECT value FROM dbo.fnc_split(@roles, ',')  

  

-- Create Temporary User Job Title Table ---  

DECLARE @tempJobTitles TABLE(user_id char(12), code char(12), description varchar(200), job_cat_id char(12), job_cat_name varchar(100))  

  

INSERT INTO @tempJobTitles(user_id, code, description, job_cat_id, job_cat_name)  

SELECT job.user_id,   

       job.code,   

       job.description,   

       job.job_cat_id,   

       cat.job_cat_name  

FROM education_security.dbo.vw_getUserJobTitles job   

     INNER JOIN dbo.vw_getAvailableJobCategories cat ON job.job_cat_id = cat.job_cat_id  

WHERE job.code IN (SELECT value FROM dbo.fnc_split(@jobtitles, ','))  

  

  

-- Create Temporary Location Table ---  

DECLARE @tempAvailLocations TABLE(location_id char(6), location_name varchar(500), region char(3), region_name varchar(500), user_id char(12), short_name varchar(500))  

  

INSERT INTO @tempAvailLocations (location_id, location_name, region, region_name, user_id, short_name)  

SELECT reg.location_id,   

       loc.location_name,   

       reg.region,   

       loc.region_name,   

       reg.user_id,   

       reg.short_name  

FROM education_security.dbo.vw_getAvailableLocationsWit  hRegion reg   

     INNER JOIN dbo.vw_getAvailableLocations loc ON reg.location_id = loc.location_id  

WHERE (reg.location_id IN (SELECT value FROM dbo.fnc_split(@locations, ','))) OR   

      (reg.region IN (SELECT value FROM dbo.fnc_split(@businessunits, ',')))  

  

  

-- Select Data From A View --  

SELECT RTRIM(usr.name_last) + ', '+ RTRIM(usr.name_first) + ' (' + RTRIM(usr.user_login) + ')' AS student_name,   

                dbo.fnc_GetStudentStatus(usr.discontinued_date, GETDATE()) AS status,   

                loc.region_name,   

                loc.location_name,   

                ttl.job_cat_name,   

                ttl.description,  

                rol.role_name  

FROM   dbo.user_master_view usr   

             INNER JOIN education_security.dbo.vw_getUsersAccessMaxLevel [max] ON usr.user_id = [max].user_id   

             INNER JOIN education_security.dbo.role_master rol ON [max].max_level = rol.[level]  

             LEFT OUTER JOIN @tempJobTitles ttl ON [max].user_id = ttl.user_id AND usr.user_id = ttl.user_id   

             LEFT OUTER JOIN @tempAvailLocations loc ON usr.user_id = loc.user_id AND usr.home_location = loc.location_id  

WHERE (usr.home_location IN (SELECT location_id FROM @locTable) OR   

                loc.region IN (SELECT region FROM @busTable)) AND   

                ttl.code IN (SELECT code FROM @jobTable) AND              

                dbo.fnc_GetCurrentStatus(@status, usr.discontinued_date,  GETDATE()) = @status AND   

               rol.role_id IN (SELECT role_id FROM @rolTable)