Início / dba / Perguntas / 4104
Accepted
VaticNZ
Asked: 2011-07-26 17:20:34 +0800 CST

Como você ORDER BY um parâmetro?

  • 772

Gostaria de saber se eu poderia solicitar algum feedback sobre um procedimento armazenado que estou executando e se há uma maneira mais eficiente de lidar com o cenário (tenho certeza de que haverá!).

Basicamente tenho um único SP que chamo para retornar uma lista de registros (Jobs) que podem ter um ou mais status e uma ordem de classificação (estou usando RowNum para paginação). No momento estou usando WITH RECOMPILE porque as variações nos status podem mudar o tempo todo (dependendo do usuário etc). Há também alguma filtragem acontecendo.

Estou usando uma instrução IF para executar essencialmente o mesmo pedaço de código com a única alteração sendo a ordem de classificação.

Acho que minhas perguntas são: Existe uma maneira melhor de fazer isso (talvez SP's diferentes para status diferentes)? Estou complicando demais as coisas devido à falta de conhecimento (bastante provável) O SP está realmente ok, mas requer pequenos ajustes para reduzir o número de linhas?

Eu colei uma parte do SP abaixo - a única diferença para o código completo são as instruções IF adicionais para as diferentes ordens de classificação ...

Eu apreciaria qualquer feedback.

Desde já, obrigado!

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

--ELSE IF para outra classificação de colunas

sql-server-2008 stored-procedures

1 respostas

  1. Best Answer

    A ordenação pode ser feita com uma expressão CASE, algo como:

    ORDER BY
    CASE WHEN @SortDirection = 'A' THEN
        CASE 
           WHEN @SortBy = 'JobNumber' THEN JobNumber
           WHEN @SortBy = 'JobId' THEN JobId 
        END
    END ASC
    , CASE WHEN @SortDirection = 'D' THEN
        CASE 
           WHEN @SortBy = 'JobNumber' THEN JobNumber
           WHEN @SortBy = 'JobId' THEN JobId 
        END
    END DESC

    Você pode querer reconsiderar as condições OR'd, pois elas provavelmente gerarão planos ruins. Um dos melhores artigos que li sobre isso (e as abordagens alternativas) é Condições de Pesquisa Dinâmica em T-SQL

    Edit: Olhando novamente para sua lista de parâmetros, os filtros primários parecem ser @CustomerId e @UserId. Sugiro criar dois procs, spJobs_SelectByCustomerId e spJobs_SelectByUserId, que filtram por seus respectivos parâmetros para que você elimine as condições '@Param = 0 ou Column = @Param'. Eu acho que o próximo parâmetro importante é @ShowCompleted (presumindo que uma vez que um trabalho é 'feito', ele não é mostrado a menos que @ShowCompleted=1), que eu consideraria incluir em índices no CustomerId e UserId.

    Edit2: Engraçado como essas perguntas às vezes ficam no fundo da sua mente! :) Na indexação do @ShowCompleted, esta é uma das ocasiões em que usar uma coluna BIT de baixa seletividade primeiro pode ser a melhor estratégia . Os índices filtrados também devem ser considerados.

    • 17

relate perguntas