Рекомендации для хранимой процедуры


Просто интересно, если я мог бы запросить отзывы о хранимой процедуре, я бегу, и есть ли более эффективный способ обработки сценария (я почти уверен, что будут!).

В принципе у меня есть один СП, что я называю, чтобы вернуться в список записей (задания), которые могут иметь один или несколько статусов и порядок сортировки (я использую параметр rownum для подкачки). На данный момент я использую при компиляции, потому что колебания на статусы могут меняться (в зависимости от пользователя и т. д.). Есть также некоторая фильтрация происходит.

Я использую оператор if, чтобы работать по существу тот же кусок кода с единственным изменением порядка сортировки.

Я думаю, мои вопросы: есть ли лучший способ сделать это (может быть разных СП для разных статусов)? Я усложнять вещи, из-за недостатка знаний (вполне вероятно) - это СП на самом деле ок, но требует незначительных настроек, чтобы уменьшить количество строк?

Я обклеил часть СП ниже - разница только в полный код является дополнительным, если отчетность за разного рода заказы...

Я бы признательны за любую обратную связь.

Заранее спасибо!

CREATE PROCEDURE [dbo].[sp_Jobs] 
(
    @PageNumber int, 
    @PageSize int, 
    @FilterExpression varchar(500), 
    @OrderBy varchar(50), 
    @CustomerID int, 
    @ShowNotSet bit, 
    @ShowPlaced bit, 
    @ShowProofed bit, 
    @ShowReProofed bit, 
    @ShowApproved bit, 
    @ShowOnTime bit, 
    @ShowLate bit, 
    @ShowProblem bit, 
    @ShowCompleted bit, 
    @ShowDispatched bit, 
    @ShowUnapproved bit, 
    @ShowClosed bit, 
    @ShowReturned bit, 
    @UserID int
)
WITH RECOMPILE    
AS    
    --JobNumber DESC 
    if @OrderBy='JobNumberDESC' 
    BEGIN 

    WITH Keys AS (SELECT TOP (@PageNumber * @PageSize) ROW_NUMBER() OVER (ORDER BY JobNumber DESC) as rn,P1.jobNumber,P1.CustID,P1.DateIn,P1.DateDue,P1.DateOut,p1.client,p1.MasterJobStatusID,p1.MasterJobStatusTimestamp,p1.OwnerID 

    FROM 
    vw_Jobs_List P1 WITH (NOLOCK) 

    WHERE 
    (@CustomerID = 0 OR CustID = @CustomerID) 
    AND (@UserID = 0 OR OwnerID = @UserID) 
    AND ((@ShowNotSet = 1 AND MasterJobStatusID=1) OR (@ShowPlaced = 1 AND MasterJobStatusID=2) OR (@ShowProofed = 1 AND MasterJobStatusID=3) OR (@ShowReProofed = 1 AND MasterJobStatusID=4) OR (@ShowApproved = 1 AND MasterJobStatusID=5) OR (@ShowOnTime = 1 AND MasterJobStatusID=6) OR (@ShowLate = 1 AND MasterJobStatusID=7) OR (@ShowProblem = 1 AND MasterJobStatusID=8) OR (@ShowCompleted = 1 AND MasterJobStatusID=9) OR (@ShowDispatched = 1 AND MasterJobStatusID=10) OR (@ShowUnapproved = 1 AND MasterJobStatusID=11) OR (@ShowClosed = 1 AND MasterJobStatusID=12) OR (@ShowReturned = 1 AND MasterJobStatusID=13)) AND (Search LIKE '%'+@FilterExpression+'%')

    ORDER BY 
    P1.JobNumber DESC ),SelectedKeys AS (
    SELECT TOP (@PageSize)SK.rn,SK.JobNumber,SK.CustID,SK.DateIn,SK.DateDue,SK.DateOut 

    FROM 
    Keys SK 

    WHERE 
    SK.rn > ((@PageNumber-1) * @PageSize) 

    ORDER BY 
    SK.JobNumber DESC) 

    SELECT SK.rn,J.JobNumber,J.OwnerID,J.Description,J.Client,SK.CustID,OrderNumber, CAST(DateAdd(d, -2, CAST(isnull(SK.DateIn,0) AS DateTime)) AS nvarchar) AS DateIn, CAST(DateAdd(d, -2, CAST(isnull(SK.DateDue,0) AS DateTime)) AS nvarchar) AS DateDue,CAST(DateAdd(d, -2, CAST(isnull(SK.DateOut,0) AS DateTime)) AS nvarchar) AS DateOut, Del_Method,Ticket#, InvoiceEmailed, InvoicePrinted, InvoiceExported, InvoiceComplete, JobStatus,j.MasterJobStatusID,j.MasterJobStatusTimestamp,js.MasterJobStatus 

    FROM SelectedKeys SK JOIN vw_Jobs_List J WITH (NOLOCK) ON j.JobNumber=SK.JobNumber JOIN tbl_SYSTEM_MasterJobStatus js WITH (NOLOCK) ON j.MasterJobStatusID=js.MasterJobStatusID 

    ORDER BY 
    SK.JobNumber DESC 
END

--Еще если на другой столбец сортировки



285
2
задан 26 июля 2011 в 01:07 Источник Поделиться
Комментарии
1 ответ

Вы можете использовать случае заявления в порядке п. Чтобы решить, какой вид для использования:

  SELECT (...)
ORDER BY CASE @OrderBy
WHEN 'JobNumberDESC' THEN sk.jobnumber
WHEN (etc.)
END DESC,
CASE @OrderBy
WHEN 'JobNumberASC' THEN sk.jobnumber
WHEN (etc.)
END ASC;

(Вам придется разделиться на восходящих и нисходящих вариантов вроде так)

Одна проблема с этим подходом заключается в том, что в вашем первоначальном заявлении я насчитал четыре места, где приказ по заявлению происходит. Это означало бы повторять длинные случае заявление большое. Я думаю, что мы можем исправить это, хотя.

Во-первых, сортировка в подзапросе, как правило, не имеет особого смысла. Я не вижу ни одной причины, чтобы иметь ключи и selectedkeys сортируют, так давайте уберем этих целях по заявлениям.

Теперь у нас осталось два заказас: той, которая используется в функции row_number функции и окончательной сортировки. Несмотря ни на что, вам нужно включить приказ в функции row_number функция, чтобы убедиться, что ваше подкачки работает правильно, но вот незадача: теперь вы можете использовать номер строки для сортировки конечного результата (т. е., изменение окончательный порядок на СК.jobnumber деск для заказа по СК.рН). Теперь есть только одно место, где необходимо динамичное заказа пункте, который намного лучше, чем четыре!

Отказ от ответственности: я не проверял эти идеи, просто мозговой штурм.


Другим вариантом было бы использовать динамический SQL. Затем вы бы что-то вроде этого:

DECLARE @OrderByClause varchar(500),
@sql varchar(max);

SET @OrderByClause =
CASE @OrderBy
WHEN 'JobNumberDESC' THEN 'sk.jobnumber DESC'
WHEN (etc)
END;

SET @sql = 'WITH keys
AS (SELECT TOP (@PageNumber * @PageSize)
ROW_NUMBER() OVER (ORDER BY ' + @OrderByClause + ') AS rn' +
(etc.);

EXEC sp_executesql @sql, (etc.);

Просто убедитесь, что вы обратите внимание на проблемы SQL-инъекций.

1
ответ дан 29 июля 2011 в 06:07 Источник Поделиться