Developing for Dynamics GP

by David Musgrave (Perth, WA, Australia) and the Microsoft Dynamics GP Developer Support Team (Fargo, ND, USA)

How to format Microsoft Dynamics GP SQL Stored Procedures

How to format Microsoft Dynamics GP SQL Stored Procedures

  • Comments 7

David Meego - Click for blog homepageHave you ever decided to have a look at one of the SQL stored procedures in your Dynamics GP databases?

If you have, you would have noticed that they are extremely difficult to read. This is because comments and formatting white space are removed by Dynamics Utilities when it creates the stored procedures. This step was taken to improve performance of the resulting code, but has the side effect of making the code impossible to read.

In the past, when I have needed to analyse code in a stored procedure, I have manually formatted the code as I read it so I can see the levels of indentation and individual commands.

A little while ago, my friend Steve Gray talked about Formatting SQL. In his post he mentioned the free Format SQL site from Redgate (the people behind the SQL Prompt tool). I had heard of SQL Prompt before, but did not require all the features it offered and did not want to buy anything. I also found that the free Format SQL site did not work well when pasting in scripts via the clipboard.

I just wanted a free online tool to format cut and pasted text from a SQL stored procedure so I can read it.

So today, I used that wonderful invention.... an internet search engine, and found a number of free tools that work well:

In no particular order:

Enjoy

David

17-Jan-2013: Just a follow up comment. Poor SQL is my preferred option, but mainly because it offers a free plug-in for SQL Server Management Studio and Visual Studio.

  • Thank you so much, David. The pain to go thru' unformatted SQL code. Not just GP's source procedures. But any SQL code written by some other developer. Those who do not have the habit of aligning their SQL Code, can now at least use the mentioned utilities and get their code aligned properly. This is very important from a developer's perspective. Thanks again.

  • Dave to the rescue again

  • Feedback so far indicates that the Poor SQL option does the best job. Depends if you like commas shown before variables or after.  In my opinion, before works better as it is easier to comment a line with double dash (--).

    David

  • If anyone prefers a nice SSMS plug in http://www.ssmstoolspack.com/ does a nice job an is a free tool. Not quite as robust as Redgate, but does a nice job. Also , each feature can be turned off if you only want certain functions.

  • Hi Adam

    Thanks for the feedback.

    David

  • Posting by Vaidy Mohan at Dynamics GP - Learn & DIscuss

    vaidymohan.com/.../formatting-sql-procedures

  • Posting by Mark Polino at DynamicAccounting.net

    msdynamicsgp.blogspot.com.au/.../how-to-format-microsoft-dynamics-gp-sql.html

Page 1 of 1 (7 items)
Comments Information

PLEASE READ BEFORE POSTING

Please only post comments relating to the topic of this page.

If you wish to ask a technical question, please use the links in the links section (scroll down, on right hand side) to ask on the Newsgroups or Forums. If you ask on the Newsgroups or Forums, others in the community can respond and the answers are available for everyone in the future.

Leave a Comment
  • Please add 3 and 2 and type the answer here:
  • Post