smacca
asked on
ORDER BY CASE Statements
Hi,
I am having difficulty writing case statements for sql (please see code below).
Any help appreciated.
Cheers.
I am having difficulty writing case statements for sql (please see code below).
Any help appreciated.
Cheers.
--region [dbo].[SelectAllMedias]
------------------------------------------------------------------------------------------------------------------------
-- Procedure Name: [dbo].[SelectAllMedias]
-- Date Generated: Wednesday, 5 November 2008
-- Author: Stephen McCormack - stephenm@mwebsolutions.com.au
-- Company: MWeb Solutions Pty Ltd
-- Software: CodeSmith v4.0.0.0
-- Template: StoredProcedures.cst
--------- --------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[SelectAllMedias]
@SortBy varchar(50) = 'CreatedDate' ,
@SortDirection varchar(4) = 'DESC'
AS
-- turn off rows affected
SET NOCOUNT ON
--isolation level
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
-- system function stores
DECLARE
@ErrStatus int, -- stores error status
@RowsAffected int -- stores number of rows affected
BEGIN
/* -------------------------------------------------------------
* Select All
* ------------------------------------------------------------- */
SELECT
[dbo].[Media].[MediaID], /* pk */
[dbo].[Media].[PhotographerID], /* fk */
[dbo].[Photographers].[PhotographerName] AS [Photographer_Name], /* fk name column */
[dbo].[Media].[JobID], /* fk */
[dbo].[Jobs].[JobTitle] AS [Job_Name], /* fk name column */
[dbo].[Media].[SiteID], /* fk */
[dbo].[Sites].[SiteName] AS [Site_Name], /* fk name column */
[dbo].[Media].[MediaTypeID], /* fk */
[dbo].[MediaTypes].[MediaTypeName] AS [MediaType_Name], /* fk name column */
[dbo].[Media].[MediaFormatID], /* fk */
[dbo].[MediaFormats].[MediaFormatName] AS [MediaFormat_Name], /* fk name column */
[dbo].[Media].[MediaTitle],
[dbo].[Media].[Description],
[dbo].[Media].[MediaUrl],
[dbo].[Media].[AdvertiserName],
[dbo].[Media].[AdvertiserNotes],
[dbo].[Media].[IsApproved],
[dbo].[Media].[ApprovedDate],
[dbo].[Media].[ApprovedBy],
[dbo].[Media].[ApprovedNotes],
[dbo].[Media].[CreatedDate],
[dbo].[Media].[CreatedBy],
[dbo].[Media].[ModifiedDate],
[dbo].[Media].[ModifiedBy]
FROM
[dbo].[Media]
LEFT OUTER JOIN [dbo].[Sites] ON [dbo].[Media].[SiteID] = [dbo].[Sites].[SiteID]
INNER JOIN [dbo].[Jobs] ON [dbo].[Media].[JobID] = [dbo].[Jobs].[JobID]
INNER JOIN [dbo].[Photographers] ON [dbo].[Media].[PhotographerID] = [dbo].[Photographers].[PhotographerID]
LEFT OUTER JOIN [dbo].[MediaTypes] ON [dbo].[Media].[MediaTypeID] = [dbo].[MediaTypes].[MediaTypeID]
LEFT OUTER JOIN [dbo].[MediaFormats] ON [dbo].[Media].[MediaFormatID] = [dbo].[MediaFormats].[MediaFormatID]
ORDER BY
CASE UPPER(@SortDirection)
WHEN 'ASC' THEN
CASE LOWER(@SortBy)
WHEN 'photographerid' THEN
[dbo].[Media].[PhotographerID]
WHEN 'jobid' THEN
[dbo].[Media].[JobID]
WHEN 'siteid' THEN
[dbo].[Media].[SiteID]
WHEN 'mediatypeid' THEN
[dbo].[Media].[MediaTypeID]
WHEN 'mediaformatid' THEN
[dbo].[Media].[MediaFormatID]
WHEN 'isapproved' THEN
[dbo].[Media].[IsApproved]
WHEN 'approveddate' THEN
[dbo].[Media].[ApprovedDate]
WHEN 'createddate' THEN
[dbo].[Media].[CreatedDate]
WHEN 'modifieddate' THEN
[dbo].[Media].[ModifiedDate]
ELSE
[dbo].[Media].[CreatedDate]
END
ASC
ELSE
CASE LOWER(@SortBy)
WHEN 'photographerid' THEN
[dbo].[Media].[PhotographerID]
WHEN 'jobid' THEN
[dbo].[Media].[JobID]
WHEN 'siteid' THEN
[dbo].[Media].[SiteID]
WHEN 'mediatypeid' THEN
[dbo].[Media].[MediaTypeID]
WHEN 'mediaformatid' THEN
[dbo].[Media].[MediaFormatID]
WHEN 'isapproved' THEN
[dbo].[Media].[IsApproved]
WHEN 'approveddate' THEN
[dbo].[Media].[ApprovedDate]
WHEN 'createddate' THEN
[dbo].[Media].[CreatedDate]
WHEN 'modifieddate' THEN
[dbo].[Media].[ModifiedDate]
ELSE
[dbo].[Media].[CreatedDate]
END
DESC
END
/* must use single statement immediately to store system functions
as all DML statements, SELECT, IF, PRINT and SET will reset @@error to zero */
SELECT
@ErrStatus = @@ERROR,
@RowsAffected = @@ROWCOUNT
-- check for errors
IF @ErrStatus <> 0
BEGIN
RAISERROR('Error occurred in stored procedure ''[dbo].[SelectAllMedias]''', 10, 1)
RETURN @ErrStatus
END
END
-- turn on rows affected
SET NOCOUNT OFF
-- success(0)
RETURN 0
--endregion
GO
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I meant after the inner CASE WHEN ... END.
--region [dbo].[SelectAllMedias]
------------------------------------------------------------------------------------------------------------------------
-- Procedure Name: [dbo].[SelectAllMedias]
-- Date Generated: Wednesday, 5 November 2008
-- Author: Stephen McCormack - stephenm@mwebsolutions.com.au
-- Company: MWeb Solutions Pty Ltd
-- Software: CodeSmith v4.0.0.0
-- Template: StoredProcedures.cst
--------- --------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[SelectAllMedias]
@SortBy varchar(50) = 'CreatedDate' ,
@SortDirection varchar(4) = 'DESC'
AS
-- turn off rows affected
SET NOCOUNT ON
--isolation level
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
-- system function stores
DECLARE
@ErrStatus int, -- stores error status
@RowsAffected int -- stores number of rows affected
BEGIN
/* -------------------------------------------------------------
* Select All
* ------------------------------------------------------------- */
SELECT
[dbo].[Media].[MediaID], /* pk */
[dbo].[Media].[PhotographerID], /* fk */
[dbo].[Photographers].[PhotographerName] AS [Photographer_Name], /* fk name column */
[dbo].[Media].[JobID], /* fk */
[dbo].[Jobs].[JobTitle] AS [Job_Name], /* fk name column */
[dbo].[Media].[SiteID], /* fk */
[dbo].[Sites].[SiteName] AS [Site_Name], /* fk name column */
[dbo].[Media].[MediaTypeID], /* fk */
[dbo].[MediaTypes].[MediaTypeName] AS [MediaType_Name], /* fk name column */
[dbo].[Media].[MediaFormatID], /* fk */
[dbo].[MediaFormats].[MediaFormatName] AS [MediaFormat_Name], /* fk name column */
[dbo].[Media].[MediaTitle],
[dbo].[Media].[Description],
[dbo].[Media].[MediaUrl],
[dbo].[Media].[AdvertiserName],
[dbo].[Media].[AdvertiserNotes],
[dbo].[Media].[IsApproved],
[dbo].[Media].[ApprovedDate],
[dbo].[Media].[ApprovedBy],
[dbo].[Media].[ApprovedNotes],
[dbo].[Media].[CreatedDate],
[dbo].[Media].[CreatedBy],
[dbo].[Media].[ModifiedDate],
[dbo].[Media].[ModifiedBy]
FROM
[dbo].[Media]
LEFT OUTER JOIN [dbo].[Sites] ON [dbo].[Media].[SiteID] = [dbo].[Sites].[SiteID]
INNER JOIN [dbo].[Jobs] ON [dbo].[Media].[JobID] = [dbo].[Jobs].[JobID]
INNER JOIN [dbo].[Photographers] ON [dbo].[Media].[PhotographerID] = [dbo].[Photographers].[PhotographerID]
LEFT OUTER JOIN [dbo].[MediaTypes] ON [dbo].[Media].[MediaTypeID] = [dbo].[MediaTypes].[MediaTypeID]
LEFT OUTER JOIN [dbo].[MediaFormats] ON [dbo].[Media].[MediaFormatID] = [dbo].[MediaFormats].[MediaFormatID]
ORDER BY
CASE UPPER(@SortDirection)
WHEN 'ASC' THEN
CASE LOWER(@SortBy)
WHEN 'photographerid' THEN
[dbo].[Media].[PhotographerID]
WHEN 'jobid' THEN
[dbo].[Media].[JobID]
WHEN 'siteid' THEN
[dbo].[Media].[SiteID]
WHEN 'mediatypeid' THEN
[dbo].[Media].[MediaTypeID]
WHEN 'mediaformatid' THEN
[dbo].[Media].[MediaFormatID]
WHEN 'isapproved' THEN
[dbo].[Media].[IsApproved]
WHEN 'approveddate' THEN
[dbo].[Media].[ApprovedDate]
WHEN 'createddate' THEN
[dbo].[Media].[CreatedDate]
WHEN 'modifieddate' THEN
[dbo].[Media].[ModifiedDate]
ELSE
[dbo].[Media].[CreatedDate]
END
ELSE
CASE LOWER(@SortBy)
WHEN 'photographerid' THEN
[dbo].[Media].[PhotographerID]
WHEN 'jobid' THEN
[dbo].[Media].[JobID]
WHEN 'siteid' THEN
[dbo].[Media].[SiteID]
WHEN 'mediatypeid' THEN
[dbo].[Media].[MediaTypeID]
WHEN 'mediaformatid' THEN
[dbo].[Media].[MediaFormatID]
WHEN 'isapproved' THEN
[dbo].[Media].[IsApproved]
WHEN 'approveddate' THEN
[dbo].[Media].[ApprovedDate]
WHEN 'createddate' THEN
[dbo].[Media].[CreatedDate]
WHEN 'modifieddate' THEN
[dbo].[Media].[ModifiedDate]
ELSE
[dbo].[Media].[CreatedDate]
END DESC
END
/* must use single statement immediately to store system functions
as all DML statements, SELECT, IF, PRINT and SET will reset @@error to zero */
SELECT
@ErrStatus = @@ERROR,
@RowsAffected = @@ROWCOUNT
-- check for errors
IF @ErrStatus <> 0
BEGIN
RAISERROR('Error occurred in stored procedure ''[dbo].[SelectAllMedias]''', 10, 1)
RETURN @ErrStatus
END
END
-- turn on rows affected
SET NOCOUNT OFF
-- success(0)
RETURN 0
--endregion
GO
The solution above doesn't solve the problem. The error returned is:
Incorrect syntax near the keyword 'DESC'.
Incorrect syntax near 'END'.
I use T-sql in SQL Server 2008
Incorrect syntax near the keyword 'DESC'.
Incorrect syntax near 'END'.
I use T-sql in SQL Server 2008
ASKER
Did you try the followup above - I correct original posting there.
Yes, i did. For this code i get:
Msg 156, Level 15, State 1, Procedure SelectAllMedias, Line 93
Incorrect syntax near the keyword 'ASC'.
Msg 156, Level 15, State 1, Procedure SelectAllMedias, Line 93
Incorrect syntax near the keyword 'ASC'.
I have done CASE WHEN order by clauses before no problem; however, I don't know if I have changed direction of sort that way and if so can't remember if will work with DESC on different line like that. You may have to have it immediately after END from case with a space in between.
i.e.
...
END DESC
You can omit the one for ASC and try that. Otherwise, please advise what issue you are having.