An alternate to CASE WHEN of SQL Server for few scenarios

Purpose of this quick post is to present an alternative way to CASE WHEN of SQL Server.

To execute the demonstration, take a look at the following example.

SELECT CustomerName,Address,Mobile FROM Customers ORDER BY CustomerName

This SQL query will retrieve the list of Customers from Customer table. Let Address and Mobile are the fields which allow NULL. It means the result of the query may return NULL for these two fields. Now we will use the result set returned by the above query to bind a Datalist or a Gridview.Let consider for Datalist, then we will use following code on .aspx page to bind these fields

Address : <%#DataBinder.Eval(Container.DataItem, "Address")%>

Mobile   :<%#DataBinder.Eval(Container.DataItem, "Mobile")%>

But, here problem is that when there is no data against one/both of these fields then on browser it will show nothing for the eval data. To avoid this we generally re-write the query using CASE WHEN as-

SELECT CustomerName,CASE WHEN Address IS NULL THEN '--Not Available--' ELSE  Address END AS  Address,CASE WHEN Mobile IS NULL THEN '--Not Available--' ELSE Mobile END AS Mobile FROM Customers ORDER BY CustomerName

The above query ensures that there is no blank space for the above said two field in the browser.

Now, for some scenario where I don’t want to set it from the server and I want an alternative then I may use Javascript to achieve this. Here the query will be same as the first query but change required on the .aspx code.

1.Declare a small Javascript method

function ReplaceEmptyFields(orig, repl) {

//orig : Origional string

//repl: String to be replaced with
 if (orig == "") {
 else {

2.Call the above Javascript in place of Databinder as below

Address : <script language="javascript">ReplaceEmptyFields('<%#DataBinder.Eval(Container.DataItem, "Address")%>', "--Not Available--")</script>
Mobile : <script language="javascript">ReplaceEmptyFields('<%#DataBinder.Eval(Container.DataItem, "Mobile")%>', "--Not Available--")</script>

This will provide the same result as previous example using CASE WHEN. Few of the advantages of using this includes html formatting and style can be applied to the result. For example, if I’ll change the above code to show ‘–Not Available–‘ in Gray color so that it will be more easy to read then the code on .aspx page will be like-

Address : <script language="javascript">ReplaceEmptyFields('<%#DataBinder.Eval(Container.DataItem, "Address")%>', "<font color='gray'>--Not Available--</font>")</script>
Mobile : <script language="javascript">ReplaceEmptyFields('<%#DataBinder.Eval(Container.DataItem, "Mobile")%>', "<font color='gray'>--Not Available--</font>")</script>

This post is based on my experience and experiments, so if you find something missing or, if you have a better idea then please share in  the comments.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s