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 ST003. This is an issue where a procedure is not enclosed in a Begin End. Here’s an example.
I started writing this proc, which looks normal. I’m not sure what isn’t being qualified with the red squiggles, as the query runs fine along. In any case, I do have a green underline on CREATE PROCEDURE, which let’s me know of a style issue.
If I put the cursor on this line, I see the lightbulb in the left margin. Since this is available, I know I have a possible “fix” that SQL Prompt will handle.
If I click this lightbulb, I see a few options.
The top one is the one I want. When I click that, SQL Prompt will reformat my query and add the BEGIN and END.
This can be controversial, as the END doesn’t stop things from being included in the procedure. For example, if I do this:
I get this:
The CREATE PROCEDURE needs to have the batch end to stop including code. The END doesn’t do this. Make sure you have the habit of ending your procedure creation with the batch separator (GO by default).
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.