Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Выборка обычного журнала  [new]
MathewSun
Member

Откуда: Москва
Сообщений: 17
Добрый день.
Строю журнал задач.
Реализовал пейджинг, фильтр и сортировку.
Взгляните пожалуйста, правильно ли работаю с параметрами собственно для пейджинга, фильтра и сортировки.
Может кто-то предложит оптимизацию запроса.

CREATE PROCEDURE  [dbo].[cpGetJournal]
(
@state int = NULL,
@ttype uniqueidentifier = NULL,
@org uniqueidentifier = NULL,
@owner uniqueidentifier = NULL,
@tstartfrom date = NULL,
@tstarttill date = NULL,
@tdeadlinefrom date = NULL,
@tdeadlinetill date = NULL,
@sort varchar(20) = 'task_number',
@dir varchar(20) = 'asc',
@size int = 10,
@page_num int = 0
)
AS
BEGIN
SET NOCOUNT ON;
	
DECLARE @first_row  int = @page_num * @size + 1
DECLARE @end_row  int = @first_row + @size - 1
	
-- Вычислим текущего пользователя -------------------------
DECLARE @emp_id uniqueidentifier = (SELECT emp_id FROM Employee WHERE emp_login = SUSER_SNAME())
	
SET DATEFORMAT DMY;

WITH up AS
(
SELECT Tasks.task_id
, @is_admin AS is_admin
, Tasks.task_number
, Tasks.task_name
, Tasks.task_desc
, Tasks.task_state
, Tasks.task_org
, Tasks.task_type
, Tasks.task_deadline
, Tasks.task_start
, Tasks.task_owner
, Employee.emp_mail AS cont_mail
, dbo.fnShortName(Employee.emp_lname, Employee.emp_fname, Employee.emp_mname) AS emp_name
, CASE WHEN @emp_id = Tasks.task_owner THEN '1' ELSE '0' END AS is_author
, TaskStates.tstate_name
, dbo.fnGetTaskStaff(Tasks.task_id) as task_staff
, Orgs.org_sname
, TaskTypes.ttype_name
, dbo.fnCanModify(@is_admin, @emp_id, Tasks.task_id, Tasks.task_owner, TaskStates.tstate_flags) AS can_modify
, TaskStates.tstate_flags
, CASE WHEN RegVisited.rv_task_id IS NULL THEN ' *' END AS visited
, ROW_NUMBER() OVER (ORDER BY
						case when @sort = 'task_name' and @dir = 'asc' 
							then Tasks.task_name end asc, 
						case when @sort = 'task_name' and @dir = 'desc' 
							then Tasks.task_name end desc,
						
						case when @sort = 'task_desc' and @dir = 'asc' 
							then Tasks.task_desc end asc, 
						case when @sort = 'task_desc' and @dir = 'desc' 
							then Tasks.task_desc end desc,
						
						case when @sort = 'task_start' and @dir = 'asc' 
							then Tasks.task_start end asc, 
						case when @sort = 'task_start' and @dir = 'desc' 
							then Tasks.task_start end desc,
						
						case when @sort = 'task_number' and @dir = 'asc' 
							then Tasks.task_number end asc, 
						case when @sort = 'task_number' and @dir = 'desc' 
							then Tasks.task_number end desc,
						
						case when @sort = 'emp_name' and @dir = 'asc' 
							then dbo.fnShortName(Employee.emp_lname, Employee.emp_fname, Employee.emp_mname) end asc, 
						case when @sort = 'emp_name' and @dir = 'desc' 
							then dbo.fnShortName(Employee.emp_lname, Employee.emp_fname, Employee.emp_mname) end desc,
							
						case when @sort = 'tstate_name' and @dir = 'asc' 
							then TaskStates.tstate_name end asc, 
						case when @sort = 'tstate_name' and @dir = 'desc' 
							then TaskStates.tstate_name end desc,
							
						case when @sort = 'tstaff' and @dir = 'asc' 
							then dbo.fnGetTaskStaff(Tasks.task_id) end asc, 
						case when @sort = 'tstaff' and @dir = 'desc' 
							then dbo.fnGetTaskStaff(Tasks.task_id) end desc,
							
						case when @sort = 'ttype_name' and @dir = 'asc' 
							then TaskTypes.ttype_name end asc, 
						case when @sort = 'ttype_name' and @dir = 'desc' 
							then TaskTypes.ttype_name end desc
					) as row_num
FROM Tasks
LEFT JOIN Orgs ON Tasks.task_org = Orgs.org_id 
LEFT JOIN TaskStates ON Tasks.task_state = TaskStates.tstate_id 
LEFT JOIN TaskFlags ON TaskStates.tstate_flags = TaskFlags.tflag_flags
LEFT JOIN TaskTypes ON Tasks.task_type = TaskTypes.ttype_id		
LEFT JOIN Employee ON Tasks.task_owner = Employee.emp_id
LEFT JOIN RegVisited ON Tasks.task_id = RegVisited.rv_task_id AND RegVisited.rv_emp_id = @emp_id
WHERE Tasks.task_id IN	(
							SELECT DISTINCT Tasks.task_id AS task_id
									FROM	TaskStaff
											LEFT JOIN Tasks ON Tasks.task_id = TaskStaff.tstaff_task_id
											LEFT JOIN TaskStates ON Tasks.task_state = TaskStates.tstate_id
									WHERE 	TaskStaff.tstaff_emp_id in (select * from fnGetEmployeesForBoss(@emp_id))
											AND (@owner IS NULL OR @owner = '00000000-0000-0000-0000-000000000000' or Tasks.task_owner = @owner)
											AND (@ttype is NULL OR (@ttype = '00000000-0000-0000-0000-000000000000' or Tasks.task_type = @ttype))
											AND (@state is null or TaskFlags.tflag_id & @state > 0)
											AND (@tstartfrom is null or Tasks.task_start > @tstartfrom )
											AND (@tstarttill is null or Tasks.task_start < @tstarttill)
											AND (@tdeadlinefrom is null or Tasks.task_deadline > @tdeadlinefrom) 
											AND (@tdeadlinetill is null or Tasks.task_deadline < @tdeadlinetill)
											AND (@org IS NULL 
												OR Tasks.task_org = @org 
												OR (@org = '00000000-0000-0000-0000-000000000000'
													AND Tasks.task_org is NULL
													)
												)
						)
)
SELECT * FROM up
WHERE row_num >= @first_row and row_num <= @end_row		
9 янв 14, 17:54    [15392456]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить