Dan,

You should be able to do this:

SELECT
Id, FaxID, ReceivedTime, Pages
FROM (

SELECT DISTINCT
[Fax].Id AS Id,
Fax.FaxID,
Fax.ReceivedTime,
Fax.Pages,
CASE sOrderBy WHEN 'Id' THEN [Fax].Id ELSE Null END AS X1,
CASE sOrderBy WHEN 'FaxID' THEN [Fax].FaxID ELSE Null END AS X2

FROM [Fax]

INNER JOIN [Order] ON [Order].FaxId = Fax.Id
INNER JOIN Sender ON Sender.Id = Fax.SenderId
INNER JOIN Prescription ON [Order].Id = Prescription.OrderId
INNER JOIN Patient ON Patient.Id = [Order].PatientId
) T

ORDER BY X1, X2

If that slows things down, you might try the following instead:

IF sOrderBy = 'Id'

SELECT DISTINCT
[Fax].Id AS Id,
Fax.FaxID,
Fax.ReceivedTime,
Fax.Pages

FROM [Fax]

INNER JOIN [Order] ON [Order].FaxId = Fax.Id
INNER JOIN Sender ON Sender.Id = Fax.SenderId
INNER JOIN Prescription ON [Order].Id = Prescription.OrderId
INNER JOIN Patient ON Patient.Id = [Order].PatientId

ORDER BY [Fax].Id

ELSE IF sOrderBy = 'FaxID

SELECT DISTINCT
[Fax].Id AS Id,
Fax.FaxID,
Fax.ReceivedTime,
Fax.Pages

FROM [Fax]

INNER JOIN [Order] ON [Order].FaxId = Fax.Id
INNER JOIN Sender ON Sender.Id = Fax.SenderId
INNER JOIN Prescription ON [Order].Id = Prescription.OrderId
INNER JOIN Patient ON Patient.Id = [Order].PatientId

ORDER BY [Fax].FaxID

ELSE

SELECT DISTINCT
[Fax].Id AS Id,
Fax.FaxID,
Fax.ReceivedTime,
Fax.Pages

FROM [Fax]

INNER JOIN [Order] ON [Order].FaxId = Fax.Id
INNER JOIN Sender ON Sender.Id = Fax.SenderId
INNER JOIN Prescription ON [Order].Id = Prescription.OrderId
INNER JOIN Patient ON Patient.Id = [Order].PatientId

Steve Kass
Drew University


Dan wrote:
>Following is my stored procedure. If I take the DISTINCT out then
>everything works fine. BUT I need the distinct because it returns
>duplicate records. If I put the DISTINCT in then I get the following
>error:
>ERROR 145: ORDER BY items must appear in the select list if SELECT
>DISTINCT is specified.
>
>
>CREATE PROCEDURE [sp_Fax_Select]
> sOrderBy varchar(50)='Id',
> iErrorCode int=-1 OUTPUT
>
>AS
>
>-- SELECT an existing row from the table.
>SELECT DISTINCT
> [Fax].Id AS Id,
> Fax.FaxID,
> Fax.ReceivedTime,
> Fax.Pages
>
> FROM [Fax]
>
> INNER JOIN [Order] ON [Order].FaxId = Fax.Id
> INNER JOIN Sender ON Sender.Id = Fax.SenderId
> INNER JOIN Prescription ON [Order].Id = Prescription.OrderId
> INNER JOIN Patient ON Patient.Id = [Order].PatientId
>
>
>ORDER BY
> CASE sOrderBy WHEN 'Id' THEN [Fax].Id ELSE Null END,
> CASE sOrderBy WHEN 'FaxID' THEN [Fax].FaxID ELSE Null END
>
>-- Get the Error Code for the statement just executed.
>SELECT iErrorCode=ERROR
>GO
>
>