ORDER BY CASE Statements

Hi,

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

Open in new window

smaccaAsked:
Who is Participating?
 
BinuthConnect With a Mentor Commented:
try this
--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
  
        BEGIN
        -- 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
 
 
                /* ------------------------------------------------------------- 
                * 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 WHEN UPPER(@SortDirection) = '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
						END ASC,
						CASE WHEN UPPER(@SortDirection) = 'DESC' 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
						END DESC
 
                               
 
                        
                /* 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
         -- turn on rows affected
        SET NOCOUNT OFF
 
        -- success(0)
        RETURN 0
        END
       
       
 
--endregion
 
GO

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
What exactly is the difficulty you are having with it?  Your case when structure looks fine, so you clearly understand that part.  

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.
0
 
Kevin CrossChief Technology OfficerCommented:
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

Open in new window

0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
VixionCommented:
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
0
 
smaccaAuthor Commented:
Did you try the followup above - I correct original posting there.
0
 
VixionCommented:
Yes, i did. For this code i get:

Msg 156, Level 15, State 1, Procedure SelectAllMedias, Line 93
Incorrect syntax near the keyword 'ASC'.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.