Words: 603

Time to read: ~ 3 minutes

Redgate have released a new functionality that ties into the SQL Prompt product: Code Analysis!

If you want to find out more about these then I suggest heading over to Simple Talk where Redgate have multiple articles published and can explain it better than I can attempt.

Wait a second, if they can explain it better than you can, what’s the point of this post?

The Point of this Post

The point of this post is that I was running into a little annoyance with mixing Code Analysis and SQL Prompt. Once I figured out a workaround for this I did what I think everyone should do and decided to spread the knowledge.

Let’s take for example using SQL Prompt to get the top 100 rows of a table, shall we?

I’ve talked before about how SQL Prompt lets you use aliases for specific commands, like gb for GROUP BY. Well we can do the same with SELECT TOP 100 * FROM and using st100.

However, when we finish off our query, I’m still seeing some squiggly lines and we all know how much I don’t like squiggly lines! (that’s still my most read post to date surprisingly)

If we click on the portion of the text with the green squiggly lines, and hit the Ctrl key, a pop-up will appear showing us why whatever we are doing is an issue.

Parentheses are the name of the game apparently which is okay for me. I use them normally when I’m writing TOP queries.

I do find it a slight annoyance that the defaults aren’t optimal but hey, we’re DBAs. I’m pretty sure that we have encountered a product with non optimal defaults before! 😉

We’ll just do now what we have done then and change them!

Change Things Up!

Now, these “snippets”, as they are called, are editable! You just have to find them. You could play hide and seek but I’m just going to tell you.

Up on the menu bar, there is a menu called “SQL Prompt” with an option of “Snippet Manager”.

Scrolling down through the snippets we can see how each one is laid out, giving us the Snippet, the Description, and the Code

So we’ve not found our snippet, and conveniently won your Hide and Seek game, we can hit the button marked “Edit…” up top and be able to do something about this code!

All we’re going to do is the simplest of changes and add brackets around the number 100.

• Please don’t forget to hit “Save”!

I cannot tell you the amount of times that I’ve not hit “Save” and freaked out because my code changes haven’t worked 🙁

Update: While this was languishing in my scheduled queue, I found out that Phil Factor ( too many blogs to mention :)| twitter ) has an amazing, in-depth post you should read about using the snippet editor.

Testing Testing Little Monkey

Now, when we run our st100 code we can see that our green squiggly lines will be replaced by solid, dependable brackets! Ah bliss!

Sure, you could ignore them but why would you want to when this is a 2 second job?

Now we can relax, safe in the knowledge that whatever green squiggly lines show up in our code, they won’t be because of this.

Red, squiggle lines are still bad though!
Definitely fix them!