Eu tenho o seguinte procedimento armazenado que funciona muito bem, EXCETO quando defino 'OrderBy' como 'OrderNumber'.
Recebo o seguinte erro: A conversão falhou ao converter o valor nvarchar 'SK11270' para o tipo de dados int.
(SK11270 é um valor na coluna OrderNumber que é nvarchar(50))
Se eu executar a consulta idêntica com qualquer outra coluna OrderBy, funcionará bem.
Estou completamente perdido e minha cabeça dói. Alguém pode ver algo óbvio que poderia estar causando esse problema?
Agradeço antecipadamente quaisquer ideias...
Rico
Isso funciona:
sp_jobs '1','20','','JobNumber','ASC','97','True','True','True','True','True','True','True','True','True','False','True','False','True','False','0'
Isso não:
sp_jobs '1','20','','OrderNumber','ASC','97','True','True','True','True','True','True','True','True','True','False','True','False','True','False','0'
PROCEDURE [dbo].[sp_Jobs]
@PageNumber int,
@PageSize int,
@FilterExpression varchar(500),
@OrderBy varchar(50),
@OrderDirection 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,
@ShowNoStock bit,
@UserID int
WITH RECOMPILE
AS
BEGIN
WITH Keys
AS (SELECT TOP (@PageNumber * @PageSize) ROW_NUMBER() OVER (
ORDER BY
CASE WHEN @OrderDirection = 'ASC' THEN
CASE
WHEN @OrderBy = 'JobNumber' THEN p1.JobNumber
WHEN @OrderBy = 'OrderNumber' THEN p1.OrderNumber
WHEN @OrderBy = 'CustID' THEN p1.CustID
WHEN @OrderBy = 'Status' THEN p1.MasterJobStatusID
WHEN @OrderBy = 'DateIn' THEN p1.DateIn
WHEN @OrderBy = 'DateDue' THEN p1.DateDue
WHEN @OrderBy = 'DateOut' THEN p1.DateOut
ELSE NULL
END
END ASC
, CASE WHEN @OrderDirection = 'DESC' THEN
CASE
WHEN @OrderBy = 'JobNumber' THEN p1.JobNumber
WHEN @OrderBy = 'OrderNumber' THEN p1.OrderNumber
WHEN @OrderBy = 'CustID' THEN p1.CustID
WHEN @OrderBy = 'Status' THEN p1.MasterJobStatusID
WHEN @OrderBy = 'DateIn' THEN p1.DateIn
WHEN @OrderBy = 'DateDue' THEN p1.DateDue
WHEN @OrderBy = 'DateOut' THEN p1.DateOut
ELSE NULL
END
END DESC) as rn ,P1.jobNumber,P1.CustID,P1.DateIn,P1.DateDue,P1.DateOut,p1.client,p1.MasterJobStatusID,p1.MasterJobStatusTimestamp,p1.OwnerID,p1.StockComplete,p1.OrderNumber
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) OR (@ShowNoStock=1 AND StockComplete=0))
AND (Search LIKE '%'+@FilterExpression+'%')),SelectedKeys
AS (SELECT TOP (@PageSize)SK.rn,SK.JobNumber,SK.CustID,SK.DateIn,SK.DateDue,SK.DateOut,SK.OrderNumber,SK.MasterJobStatusID
FROM Keys SK
WHERE SK.rn > ((@PageNumber-1) * @PageSize))
SELECT SK.rn,J.JobNumber,J.OwnerID,J.Description,J.Client,SK.CustID,SK.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,StockComplete
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
END
