Menu
Free Pack
Download BuildMaster Free Trial

Using Source Control to Manage Stored Procedures

by Olivia Glenn-Han, on Mar 5, 2015 10:30:00 AM

It goes without saying that source code files belong in source control. Using your production environment as the repository is not only difficult (for compiled applications, at least) but is in fact one of the worst possible practices. Code has to start in source control and move its way up to production.

Of course, the opposite is true for data: it has to live in production. That means changes to your data structures are tricky, as you can't just compile a new database and push it to production. But remember, not everything in a database is data. In fact, just about every object in the database except tables don't actually store data

Unlike tables, you can DROP and CREATE stored procedures, triggers, views, and functions a hundred times over without impacting data. These objects are code that just happen to exist inside of a database instead of a file system (and you know where code belongs).

The rules of database objects in source control are simple: one file per object (named after the object) that will DROP and then CREATE the object. You can use a tool like Scriptio to extract existing objects into files. Throw in a little file organization, and you're ready for check-in:

            1.FUNCTIONS\
               1.FormatOrderNumber.sql
            2.VIEWS\
               1.OrdersWithTotals.sql
               1.SalesReport.sql
               2.OrdersPastDue.sql
            3.PROCS\
               1.AddItemToOrder.sql
               1.ValidateOrder.sql
               2.ProcessOrder.sql
        

Note that the numeric prefixes ensure proper execution order. Procedures can use Views, which in turn can use functions, but usually not the other way around. Since a handful database objects depend on other objects, those can prefixed with "2." instead of "1.".

Execution of these scripts is just as easy and can be accomplished with a simple batch file:

              FOR /R %%f IN (*.sql) DO (
              ECHO Running %%f
              OSQL -E -i "%%f" -n -b -d myDatabase -S myServer
            )
        

With scripts and a batch script set-up in source control, building your database code is as easy as grabbing from source control and clicking "execute scripts."

BuildMaster makes it easy to execute these scripts. Check out our Database features to learn more.

Download BuildMaster Free Trial

Topics:BuildMaster

Related Posts

About Inedo

Inedo is a software product company bringing you the "tech behind the tech."

Makers of Windows-first, enterprise DevOps tools BuildMaster CI/CD, ProGet private package management, and Otter IaC. Maximize developer time, minimize release risk, and empower stakeholders to bring their vision to life faster, all with the people and technology you have right now.

Follow us on social media

Follow Inedo Twitter New call-to-action Follow Inedo on YouTube Follow Inedo on Facebook

Free e-books

Free CICD Book Free dotnet book free IaC book Jenkins CICD Guide