?
Solved

ORDER BY CASE Statements

Posted on 2008-11-05
6
Medium Priority
?
838 Views
Last Modified: 2009-11-25
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

0
Comment
Question by:smacca
6 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22884766
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
 
LVL 14

Accepted Solution

by:
Binuth earned 2000 total points
ID: 22884833
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22885119
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 

Expert Comment

by:Vixion
ID: 25907841
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
 

Author Comment

by:smacca
ID: 25908199
Did you try the followup above - I correct original posting there.
0
 

Expert Comment

by:Vixion
ID: 25909232
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

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question