Using CASE statements to control program logic

Using CASE statements to control program logic

You can't do this, but there is a way round it sometimes.  To explain what I mean by this, consider this example query to print out a message according to how many short films there are in a table:

IF EXISTS(SELECT * FROM tblFilm WHERE FilmRunTimeMinutes < 60)

PRINT 'There are some really short films'

ELSE

IF EXISTS(SELECT * FROM tblFilm WHERE FilmRunTimeMinutes < 90)

PRINT 'There are some shortish films'

ELSE

PRINT 'No short films found'

This uses subqueries, but hopefully it's reasonably clear what's going on.  This nested IF condition is hard to read, and will get harder as we add more possible conditions.  It would be much easier to read and write using CASE, but the following won't work:

CASE

WHEN EXISTS(SELECT * FROM tblFilm WHERE FilmRunTimeMinutes < 60)

THEN PRINT 'There are some really short films'

WHEN EXISTS(SELECT * FROM tblFilm WHERE FilmRunTimeMinutes < 90)

THEN PRINT 'There are some shortish films'

ELSE

PRINT 'No short films found'

END

This is because you can't use CASE to control program flow.  However, you can use it to calculate expressions, leading to the following work-round:

-- variable to hold message to display

DECLARE @Message varchar(MAX)

-- work out what should be displayed

SET @Message =

CASE

WHEN EXISTS(SELECT * FROM tblFilm WHERE FilmRunTimeMinutes < 60)

THEN 'There are some really short films'

WHEN EXISTS(SELECT * FROM tblFilm WHERE FilmRunTimeMinutes < 90)

THEN 'There are some shortish films'

ELSE

'No short films found'

END

-- print this out

PRINT @Message

sorting perfomanance issue

Sorting:

While working on some DMV scripts, I came up with a lazy way to have a user definable sort order in the query that seemed like pure genius. I showed it to the team and they’d never seen anything like it before.

The Situation

Users like to be in control. They want to define custom columns, sort orders, and basically drag, drop, and pivot chart their way to victory. While I’m not going to show you how to build custom everything, we can look at a custom sort order.
Let’s start with a simple query:

SELECT  SalesOrderNumber, OrderDate, DueDate, ShipDate,

PurchaseOrderNumber, AccountNumber, SubTotal,

TaxAmt, Freight, TotalDue

FROM    Sales.SalesOrderHeader

Possible Solutions

Our users want to be able to define a custom sort order on this query. We could solve this in a few ways:

1. Writing several stored procedures
2. Use dynamic SQL to build an ORDER BY

Writing several stored procedures is tedious and error prone – it’s possible that a bug can be fixed in one of the stored procedures but not the others. This solution also presents additional surface area for developers and DBAs to test and maintain. The one advantage that this approach has is that each stored procedure can be tuned individually. For high performance workloads, this is a distinct advantage. For everything else, it’s a liability.
We could also use dynamic SQL to build an order clause. I wanted to avoid this approach because it seemed hacky. After all, it’s just string concatenation. I also wanted to work in the ability for users to supply a top parameter without having to use the TOP operator.

The First Attempt

My first attempt at rocket science looked like this:

DECLARE @SortOrder VARCHAR(50) = 'OrderDate';

SELECT  rn,

SalesOrderNumber,

OrderDate,

DueDate,

ShipDate,

PurchaseOrderNumber,

AccountNumber,

SubTotal,

TaxAmt,

Freight,

TotalDue

FROM    (

SELECT  CASE @SortOrder WHEN 'OrderDate'

THEN ROW_NUMBER() OVER

(ORDER BY OrderDate DESC)

WHEN 'DueDate'

THEN ROW_NUMBER() OVER

(ORDER BY DueDate DESC)

WHEN 'ShipDate'

THEN ROW_NUMBER() OVER

(ORDER BY ShipDate DESC)

END AS rn,

SalesOrderNumber,

OrderDate,

DueDate,

ShipDate,

PurchaseOrderNumber,

AccountNumber,

SubTotal,

TaxAmt,

Freight,

TotalDue

FROM    Sales.SalesOrderHeader

) AS x

ORDER BY rn ASC;

Why do it this way? There are a few tricks with paging that you can perform using ROW_NUMBER() that I find to be more readable than using OFFSET and FETCH in SQL Server 2012. Plus ROW_NUMBER() tricks don’t require SQL Server 2012.
Unfortunately, when I looked at the execution plan for this query, I discovered that SQL Server was performing three separate sorts – one for each of the case statements. You could generously describe this as “less than optimal”.

Look at all those pretty sorts!

Even though it seems like SQL Server should optimize out the CASE statement, the obvious thing doesn’t happen. SQL Server has to compute the ROW_NUMBER() for every row in the result set and then evaluate the condition in order to determine which row to return – you can even see this in the first execution plan. The second to last node in the plan is a Compute Scalar that determines which ROW_NUMBER() to return.
I had to dig in and figure out a better way for users get a custom sort option.

Moving the CASE to the ORDER BY

My next attempt moved the custom sort down to the ORDER BY clause:

DECLARE @SortOrder VARCHAR(50) = 'OrderDate';

SELECT  SalesOrderNumber,

OrderDate,

DueDate,

ShipDate,

PurchaseOrderNumber,

AccountNumber,

SubTotal,

TaxAmt,

Freight,

TotalDue

FROM    Sales.SalesOrderHeader

ORDER BY CASE @SortOrder WHEN 'OrderDate'

THEN ROW_NUMBER() OVER

(ORDER BY OrderDate DESC)

WHEN 'DueDate'

THEN ROW_NUMBER() OVER

(ORDER BY DueDate DESC)

WHEN 'ShipDate'

THEN ROW_NUMBER() OVER

(ORDER BY ShipDate DESC)

END ASC

This ended up performing worse than the first attempt (query cost of 8.277 compared to the original query’s cost of 6.1622). The new query adds a fourth sort operator. Not only is the query sorting once for each of the possible dates, it’s then performing an additional sort on the output of the ROW_NUMBER() operator in the ORDER BY. This clearly isn’t going to work.

-50% improvement is still improvement, right?

Getting Rid of ROW_NUMBER()

It seems like ROW_NUMBER() really isn’t necessary for our scenario. After all – I only added it as a trick if so I could potentially add paging further down the road. Let’s see what happens if we remove it from the query:

DECLARE @SortOrder VARCHAR(50) = 'OrderDate';

SELECT  SalesOrderNumber,

OrderDate,

DueDate,

ShipDate,

PurchaseOrderNumber,

AccountNumber,

SubTotal,

TaxAmt,

Freight,

TotalDue

FROM    Sales.SalesOrderHeader

ORDER BY CASE @SortOrder WHEN 'OrderDate' THEN OrderDate

WHEN 'DueDate' THEN DueDate

WHEN 'ShipDate' THEN ShipDate

END DESC

Right away, it’s easy to see that the query is a lot simpler. Just look at the execution plan:

A contender appears!

This new form of the query is a winner: the plan is vastly simpler. Even though there’s a massive sort operation going on, the query is still much cheaper – the over all cost is right around 2 – it’s more than three times cheaper than the first plan that we started with.
There’s one downside to this approach – we’ve lost the ability to page results unless we either add back in the ROW_NUMBER() or else use FETCH and OFFSET.

Bonus Round: Back to ROW_NUMBER

While using my brother as a rubber duck, he suggested one last permutation – combine the ORDER BY technique with the ROW_NUMBER() technique:

DECLARE @SortOrder VARCHAR(50) = 'OrderDate';

SELECT  rn,

SalesOrderNumber,

OrderDate,

DueDate,

ShipDate,

PurchaseOrderNumber,

AccountNumber,

SubTotal,

TaxAmt,

Freight,

TotalDue

FROM    (

SELECT  ROW_NUMBER() OVER (ORDER BY CASE @SortOrder

WHEN 'OrderDate' THEN OrderDate

WHEN 'DueDate' THEN DueDate

WHEN 'ShipDate' THEN ShipDate

END DESC)

AS rn,

SalesOrderNumber,

OrderDate,

DueDate,

ShipDate,

PurchaseOrderNumber,

AccountNumber,

SubTotal,

TaxAmt,

Freight,

TotalDue

FROM    Sales.SalesOrderHeader

) AS x

ORDER BY rn ;

This ended up being almost as fast as the ORDER BY approach; this query’s cost is only 0.00314 higher than the ORDER BY. I don’t know about you, but I would classify that as “pretty much the same”. The advantage of this approach is that we get to keep the ROW_NUMBER() for paging purposes, there is only one sort, and the code is still relatively simple for maintenance and development purposes.
Check out the plan!

Victory is ours!

What Did We Learn?

I learned that trying to be smarter than SQL Server can lead to pretty terrible performance. It’s important to remember that the optimizer reserves the right to re-write. In the first and second case, SQL Server did me a favor by re-writing the query. Our third case is fairly obvious. The fourth example is somewhat surprising – by shifting around the location of theCASE, we’re able to eliminate multiple sorts and gain the benefit of using ROW_NUMBER().

Sorting and paging in sql server 2008

In the following example I am performing searching, sorting and paging against an Employee table.

I will be searching and sorting against EmployeeId, First Name, Last Name and Title and will be paging for 5 in each page.

Create one Stored Procedure (named usp_GetAllEmployees) by defining the following input parameters.

@EmployeeID int, -- Search option Employee Id

@FirstName varchar(40),-- Search option Employee First Name

@LastName varchar(50), -- Search option Employee Last Name

@Title varchar(100), -- Search option Employee Title

@FirstRow int, -- Starting Row Number

@LastRow int, -- Ending Row Number

@SortColumn varchar(30),-- Sort Column Name

@SortOrder varchar(4) -- Sort Direction (ASC/DESC)

Now define the sorting condition as in the following. Sorting can always occur against one column at a time. Therefore I will use the ROW_NUMBER function to sort the rows against the particular column and get the row number for each record. The ROW NUMBER is required for Paging.

-- Combining Sort Column and Sort order

SET @OrderBy=@SortColumn+@SortOrder

SELECT ROW_NUMBER() OVER(ORDER BY

-- Sorting EmployeeID as Ascending order

CASE

WHEN @OrderBy = 'EmployeeIDASC' THEN EmployeeID

END ASC,

-- Sorting EmployeeID as Descending order

CASE

WHEN @OrderBy = 'EmployeeIDDESC' THEN EmployeeID

END DESC)

Define all output columns

Put a filter condition in the WHERE clause as in the following. The following statement will check the @FirstName parameter for null or empty or that the FirstName column value matches the parameter value. By writing like this you can have more than one filter condition from your application. It is not necessary to pass the value for every Search parameter.
 

WHERE

-- Filtering FirstName

(@FirstName IS NULL OR @FirstName = '' OR FirstName LIKE @FirstName)

Write one Sub query to fetch the filtered and sorted output for paging purposes. Until now we have fetched all the records by applying the filter condition.

Paging can be done now by using the ROW NUMBER value as in the following. The @FirstRow will contain the Start Page number and @LastRow will contain the End Page number. In this example it will be @FirstRow=1 and @LastRow=5. (Note: you can determine the Start and End page number from your application by setting the Page Size).
 

SELECT * FROM

(

)AS EmployeesData

WHERE RowNum >=@FirstRow AND RowNum<=@LastRow

-- Taking only the given range records

Refer to the following complete Stored Procedure:

CREATE PROCEDURE [dbo].[usp_GetAllEmployees]

(

@EmployeeID int, -- Search option Employee Id

@FirstName varchar(40), -- Search option Employee First Name

@LastName varchar(50), -- Search option Employee Last Name

@Title varchar(100), -- Search option Employee Title

@FirstRow int, -- Starting Row Number

@LastRow int, -- Ending Row Number

@SortColumn varchar(30),-- Sort Column Name

@SortOrder varchar(4) -- Sort Direction (ASC/DESC)

)

AS

SET NOCOUNT ON

DECLARE @OrderBy varchar(40)

-- Setting Sort Column and Sort Order null for empty value

IF @SortColumn IS NULL

SET @SortColumn=ISNULL(@SortColumn,'')

IF @SortOrder IS NULL

SET @SortOrder=ISNULL(@SortOrder,'')

-- Combining Sort Column and Sort order

SET @OrderBy=@SortColumn+@SortOrder

SELECT * FROM

(

SELECT ROW_NUMBER() OVER(ORDER BY

-- Sorting EmployeeID as Ascending order

CASE

WHEN @OrderBy = 'EmployeeIDASC' THEN EmployeeID

END ASC,

-- Sorting EmployeeID as Descending order

CASE

WHEN @OrderBy = 'EmployeeIDDESC' THEN EmployeeID

END DESC,

-- Sorting FirstName as Ascending order

CASE

WHEN @OrderBy = 'FirstNameASC' THEN FirstName

END ASC,

-- Sorting FirstName as Descending order

CASE

WHEN @OrderBy = 'FirstNameDESC' THEN FirstName

END DESC,

-- Sorting LastName as Ascending order

CASE

WHEN @OrderBy = 'LastNameASC' THEN LastName

END ASC,

-- Sorting LastName as Descending order

CASE

WHEN @OrderBy = 'LastNameDESC' THEN LastName

END DESC,

-- Sorting Title as Ascending order

CASE

WHEN @OrderBy = 'TitleASC' THEN Title

END ASC,

-- Sorting Title as Descending order

CASE

WHEN @OrderBy = 'TitleDESC' THEN Title

END DESC,

-- Default Sorting by EmployeeID

CASE

WHEN @OrderBy = '' THEN EmployeeID

END ASC

) AS RowNum,

COUNT (*) OVER () AS TotalEmployees,

EmployeeID,

FirstName,

LastName,

Title

FROM

Employees WITH(NOLOCK)

WHERE

-- Filtering EmployeeID

(@EmployeeID IS NULL OR @EmployeeID = 0 OR EmployeeID=@EmployeeID) AND

-- Filtering FirstName

(@FirstName IS NULL OR @FirstName = '' OR FirstName LIKE @FirstName) AND

-- Filtering LastName

(@LastName IS NULL OR @LastName = '' OR LastName LIKE @LastName) AND

-- Filtering Title

(@Title IS NULL OR @Title = '' OR Title LIKE @Title)

)AS EmployeesData

WHERE RowNum >=@FirstRow AND RowNum<=@LastRow -- Taking only the given range records

SET NOCOUNT OFF

  

Refer to the following example for the same.

1. Without any Search and Sorting option. The default condition is to load all records within the page range.

The following query will result in five records:

exec usp_GetAllEmployees @EmployeeID=null,@FirstName='',@LastName='',@Title='',@FirstRow=1,@LastRow=5,@SortColumn='',@SortOrder=''



You can see in the preceding result that there is a total 9 records.

2. With the Sorting option. I will be sorting against the First Name:

exec usp_GetAllEmployees @EmployeeID=null,@FirstName='',@LastName='',@Title='',@FirstRow=1,@LastRow=5,@SortColumn='FirstName',@SortOrder='ASC'

It will return 5 records with the FirstName sorted:



3. With Searching. I will be searching against LastName:

exec usp_GetAllEmployees @EmployeeID=null,@FirstName='',@LastName='Fuller',@Title='',@FirstRow=1,@LastRow=5,@SortColumn='',@SortOrder=''

It will return 1 record:



4. With Searching and Sorting. I will be sorting and searching against the First Name (containing):

exec usp_GetAllEmployees @EmployeeID=null,@FirstName='%An%',@LastName='',@Title='',@FirstRow=1,@LastRow=5,@SortColumn='FirstName',@SortOrder='ASC'

It will return 4 records:



5. With multiple searching, I will be searching against the First Name, Last Name and Ttile (containing):

exec usp_GetAllEmployees @EmployeeID=null,@FirstName='%An%',@LastName='%d%',@Title='Sales Representative',@FirstRow=1,@LastRow=5,@SortColumn='',@SortOrder=''

It will return 2 records:

The usages of doing searching, sorting and paging in a database level is to improve your application.