Voice of the DBA

Tag: syndicated

SQL Prompt Fixes Always Have an Else
SQL Prompt is a fantastic coding aid, but it does more than format your code and provide intellisense. Over time, the team has enhanced SQL Prompt to also guide you along and fix some bad code that your team might write out of habit.

SQL Prompt 10.1 released recently, and one of the recent fixes is for an issue that we’ve denoted as BP012. This is an issue where there is a CASE statement in code, but no ELSE has been defined. An example of this is shown below:
```
SELECT 
  oli.OrderLineItemKey
, oli.OrderID
, oli.qty
, CASE WHEN oli.qty < 10
         THEN oli.unitcost
     WHEN  oli.qty >= 10 AND oli.qty < 20
         THEN oli.unitcost * .05
     WHEN  oli.qty >= 20 AND oli.qty < 100
         THEN oli.unitcost * .1
     END AS UnitCost
, oli.linetotal
  FROM dbo.OrderLineItem AS oli
```
This is a problem because we often find some weird data being entered. You might think there will never be more than 100 ordered, but who knows. We should always have an ELSE clause, just in case.

Why? Here is why.

NULL is returned if you haven’t accounted for something.

If you hover over, you get the issue in a tip. If you put the cursor on the line, you get the lightbulb with the fixes on the side.

As with other fixes, you can click the light bulb and see the fixes.

When we click the top item, SQL Prompt will add an ELSE clause, with a placeholder and the cursor is here. You can start typing, and enter the expression or value that is appropriate.

This is another place where junior (or overloaded) developers may make a mistake. Having this in SQL Prompt allows this to be caught by the individual developer. Having this in SQL Code Guard (part of SQL Prompt and our automated build/deploy tools) and an automated build also means the code is evaluated automatically and this certainly doesn’t go to QA.

If you haven’t tried SQL Prompt, download an eval and see what you think. If you have it, upgrade and ensure you have all the code fixes.
April 22, 2020
Daily Coping 22 Apr 2020

I’ve started to add a daily coping tip to the SQLServerCentral newsletter, which is helping me deal with the issues in the world. I’m adding my responses for each day here.

Today’s tip is to look for the good in others and notice their strengths.

This has been a trying time, and the stress, the close quarters, the lack of variety, it’s made many of us cranky. Certainly it’s made me cranky at times.

Twitter is the worst, but all social media can test one’s patience. It doesn’t really matter which way you lean on an issue, there are plenty of people that will annoy you. Since social media is a bit of my job, I’m slightly trapped at times.

What I’ve done is to a) try to stop reacting, and b) appreciate the other point of view. I know most people with a strong opinion that differs from mine are not looking to make things worse. They often have a reason for their own view of the world, and I am trying to take a deep breath, respect that, and then think about things from their point of view. It’s helping.

When I can actually avoid reacting quickly.

April 22, 2020
DAX and Power BI Stairway Live
As part of the Redgate Community Circle, I’m going to be running a live class on DAX and Power BI, every Thursday, from 12MDT for about 30-45 minutes. We’ll see how things go.

I’ve been wanting to learn DAX and I keep putting it off. I can google with the best of them and fumble through things, but that’s not ideal. And it might not be correct, as I’m not sure I’m always writing the code that I need, or the best code. So I want to walk through Bill Pearon’s Stairway to DAX and Power BI live.

Join me and we’ll work through the series, learning some DAX and applying it to Power BI, which will be interesting. I will read the articles ahead of time, and download anything I need, but for the most part, I’ll be fumbling along with you.

If you’ve got time, join me live. The webinar link is here, and you’re welcome to join, for the whole time or just a part. I anticipate things going for 30+ minutes, but I’ll cut off at an hour. Feel free to participate and as long as we don’t have too many people, we can all discuss the article and try things out live.

For security, I’m not directly posting details, but here is some of it.
- Date: Thursday, Apr 23, 2020 (repeating each Thur for 8 weeks or so)
- Time: 12:00pm MDT/2:00pm EDT/7pm GMT
- Webinar link: https://redgate.zoom.us/j/91925023130?pwd=YWc4T2JFMzdibDRyTlhGZnZLemQyZz09
- Question? Ping me on Twitter (@way0utwest)
April 21, 2020
Daily Coping 21 Apr 2020

I’ve started to add a daily coping tip to the SQLServerCentral newsletter, which is helping me deal with the issues in the world. I’m adding my responses for each day here.

Today’s Tip is: Connect with nature. Breathe and notice life continuing.

That’s an easy one for me. I live on a ranch, and get outside regularly with the dogs or helping with horses. Lately we’ve been rehabbing a dog after surgery, so I get out there a few times a day and walk her around, trying to build muscle.

As I do, I make it a point to appreciate the clean, fresh air, to look at the amazing sky and wonderful world. I’m blessed and lucky I get to live in a beautiful place. Here are two consecutive days in CO recently.

I also appreciate the wildlife around. I stopped on the way home to snap this shot of my neighbor’s property since I could see mine in the background to the right.

Nature and animals make our lives richer and I try to remember that.

April 21, 2020