Sorting ASC or DESC through the order by 1

[traveller4]

My problem is I can pass the first ORDER BY SORT but I cannot get the ASC or DESC to work

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO








ALTER PROC UDF_##CurLitFiles_Move

(
@SortField char(20),
@Order tinyint
)
AS
IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name ='##CurLitFiles2')
DROP TABLE [##CurLitFiles2]




BEGIN



SELECT
[FileID],
[FileName],
[ClientName],
[MinistryName],
[SecMinName],
[SecClientName],
[LevelOfCourt],
[ActionNo],
[CourtLoc],
[Phase],
[CatOfLaw],
[OpenDate],
[CloseDate],
[Profile],
[FileManager],
[LawGroupLoc] INTO ##CurLitFiles2
FROM [##CurLitFiles]

ORDER BY
CASE
WHEN @SortField = 'FileName' THEN [FileName]

+ ' ' + CASE
WHEN @Order = 1 THEN 'DESC' ELSE 'ASC'


END
END
END



BEGIN

EXEC UDF_CurLitFiles_All

END









GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

thanks in advance

Micheal

 

[SQLBill]

Are you getting an error message? If so, what is it?

Try this and let me know what happens....

+ ' ' + (CASE
WHEN @Order = 1 THEN 'DESC' ELSE 'ASC')

-SQLBill

 

[traveller4]

Thanks SQLBill for your quick response

There is no error message the @order is ignored completely

I put + ' ' + (CASE
WHEN @Order = 1 THEN 'DESC' ELSE 'ASC')
and got an error messsage

but changed it to

+''+(CASE
WHEN @Order = 1 THEN 'DESC' ELSE 'ASC'


END)

and no error messsage but the @order is still being ignored

Micheal

 

[swampBoogie]

Desc is a qualifier not a value

 ORDER BY   
    case when @sortorder = 0 then 
    CASE WHEN @SortField = 'FileName' THEN FileName 
         when @SortField = '...' THEN ... end end asc,
    case when @sortorder = 1 then
    CASE WHEN @SortField = 'FileName' THEN FileName 
         when @SortField = '...' THEN ... end end desc

There are some other alternatives

1) use dynamic SQL
2) multiple statements with different order by clauses
(may yield much code)

 

[traveller4]

Thank you swampBoogie

I am on my way now I am actually trying to put forth three orde by sorts on any of my fields so I will be coding for awhile

Thanks agian I was banging my head for about 5 hours now

Micheal