ORDER BY CASE Statements

--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

Select allOpen in new window