SQL Server - How to create code versioning of your Stored Procedures in HTML and with change comments

Views: 1.271
Reading Time: 7 minutes

Hey guys!

In this article I will share with you how to create a code version of your Stored Procedures in HTML and with comments on the change. In the past, I had already created the post How to create an Audit trigger to log object manipulation in SQL Server, which shows how to write to a database table all changes made to a database, saving information about who changed, when the change was made, which object was changed, and the DDL / DCL T-SQL script used to make that change. in the bank.

In this post I go a little further, creating a trigger that logs the changes in a table as well, but force the use of a specific tag that tells the reason for the change in this Stored Procedure (does not allow changing SP's without the reason or use the previous reason) and at the end of the update, generates object-organized HTML pages with all of that object's documentation and change history.

Source code for objects used in this article

View information

Used table

Log_Procedures

Functions Used

fncDocumentacao_SearchTag

Stored Procedures Used

stpDOC_geraBloco_Versoes

stpWrite_File_File

stpDOC_Gera_Archives

Used trigger

trgDDLAuditQuery

Remembering that as I use the OLE Automation feature in the Stored Procedure stpWrite_File_OF to write the files to disk, we will need enable this feature on instance:

How to version codes

Once you have created all the objects required for this control, you will find that you will no longer be able to change an SP without stating the reason for the change:

Simple way to document changes:

Complete way to document changes:

Some examples of the final result

Homepage

Viewing the 2 Version

Viewing the 1 Version

That's it folks!
I hope you enjoyed this post and see you next time!