Feeds:
Posts
Comments

Posts Tagged ‘CASE statement’

SQL SERVER – How to implement conditional order by clause

Posted in Solutions, SQL SERVER, tagged , , , , , on August 21, 2015| 2 Comments »

Earlier this week, my team & I were working on financial reports and we developed some giant scripts in order to generate report data. Once we were done with the report, we came to know that we did not implement dynamic sorting in the report. Ooopssss !!!. Now do we need to re-write the query and convert into dynamic query ? 😦 Of course NOT.
What you can actually do is, write a conditional case and make it dynamic sort but it will increase the size of your query depending upon how many conditions you have.

In today’s post I will implement the dynamic sorting using CHOOSE function & CASE Statement. First of all, I will find the column number using CASE Statement and then pass the Column order number to CHOOSE function to actually sort the column. It will actually reduce the size of your query.

Given below is the script which will dynamically sort the query.

Use AdventureWorks2014
GO

DECLARE @SortCoulmnName VARCHAR(50) = 'OrderDate';
DECLARE @SortColumnNumber AS INT

SET @SortColumnNumber = CASE
When @SortCoulmnName='SalesOrderID' THEN 0
WHEN @SortCoulmnName='OrderDate' THEN 1
WHEN @SortCoulmnName='DueDate' THEN 2
WHEN @SortCoulmnName='ShipDate' THEN 3
ELSE 0
END
-- By Default, it will sort on first column

SELECT SalesOrderID
,OrderDate
,DueDate
,ShipDate
,Status
FROM
Sales.SalesOrderHeader
ORDER BY
CHOOSE(@SortColumnNumber,SalesOrderID,OrderDate,DueDate,ShipDate) DESC
GO

dynamic order by 1.1

Let me know if you came across this issue and its solution as well.

Read Full Post »

SQL Server – Multiple ways to achieve Dynamically Order By Clause

Posted in Solutions, SQL SERVER, tagged , , , , , , , , , , on May 18, 2013| 1 Comment »

‘Dynamically order by’ clause of any query result set is an old topic and has been discussed on multiple forums. Mostly the solution is being achieved using CASE statement. But what encourages me to write this article is that we can achieve the same with shorter code using  IIF & CHOOSE logical functions shipped with SQL Server 2012.

Method 1 :

In this method we will use the traditional approach and dynamically order by the query result set using CASE statement. Given below is the script.

USE AdventureWorks2012
GO
DECLARE @ORDERBY INT
SET @ORDERBY=3

SELECT
BusinessEntityID
, FirstName
, LastName
, JobTitle
FROM HumanResources.vEmployee
ORDER BY
CASE
WHEN @ORDERBY=1 THEN FirstName
WHEN @ORDERBY=2 THEN LastName
WHEN @ORDERBY=3 THEN JobTitle
END
GO
--OUTPUT

dynamicorderby1.1

Method 2 :
In this method we will use a NEW approach and dynamically order by the query result set using IIF logical function shipped in SQL Server 2012. Given below is the script.

USE AdventureWorks2012
GO
DECLARE @ORDERBY INT
SET @ORDERBY=3

SELECT
BusinessEntityID
, FirstName
, LastName
, JobTitle
FROM HumanResources.vEmployee
ORDER BY
IIF(@ORDERBY=1,FirstName,IIF(@ORDERBY=2,LastName,JobTitle))
--OUTPUT

dynamicorderby1.1

Method 3 :
In this method we will use yet another NEW approach and dynamically order by the query result set using CHOOSE logical function shipped in SQL Server 2012. Given below is the script.

USE AdventureWorks2012
GO
DECLARE @ORDERBY INT
SET @ORDERBY=3

SELECT
BusinessEntityID
, FirstName
, LastName
, JobTitle
FROM HumanResources.vEmployee
ORDER BY
CHOOSE(@ORDERBY,FirstName,LastName,JobTitle)
GO
--OUTPUT

dynamicorderby1.1

Conclusion :
In all of the above methods we used different methods, functions, approaches but the output will remain same and also it will not impact performance. It just reduces the line of code.

Read Full Post »

SQL SERVER – How To Convert From Boolean(bit) to String

Posted in Solutions, SQL SERVER, tagged , , , , , , on May 14, 2013| Leave a Comment »

Bit (Boolean) data type keeps result in 1 or 0 form in the SQL Server database. But most of the time we need to display 0 as FALSE and 1 as TRUE in front end applications. Given below is the shortest possible solution using IIF function in SQL Server 2012.

You can also convert it using CASE statement but the syntax will be longer.

Given below are the two methods you can use to convert Boolean(bit) data type into string.

Method 1:
In this method, we will use IIF function to convert boolean(bit) to string. IIF function is a new logical function shipped with SQL Server 2012.

--This script is compatible with SQL Server 2012 and above.
USE AdventureWorks2012
GO
SELECT
[BusinessEntityID]
, [Name]
, [ActiveFlag]
, IIF([ActiveFlag]=1,'TRUE','FALSE') AS [ActiveFlag As String]
FROM [Purchasing].[Vendor]
GO
--OUTPUT

boolean_to_string1.1

Method 2:
In this method, we will use CASE statement to convert boolean(bit) to string.

--This script is compatible with SQL Server 2005 and above.
USE AdventureWorks2012
GO
SELECT
[BusinessEntityID]
, [Name]
, [ActiveFlag]
, CASE WHEN [ActiveFlag]=1 THEN 'TRUE'
WHEN [ActiveFlag]=0 THEN 'FALSE'
END AS  [ActiveFlag As String]
FROM [Purchasing].[Vendor]
GO
--OUTPUT

boolean_to_string1.2

Conclusion :
The only difference between the above two methods is the Syntax. Apart from this, each and every activity, including the performance, will be the same. As per MSDN, IIF is a shorthand form for writing a CASE expression.

Read Full Post »