How to add Stored Procedures to Version Control

Our team just experienced for the first time the hassle of not having version control for our DB. How can we add stored procedures at the very least to version control? The current system we’re developing relies on SPs mainly.

  • Retrieve previous version of a stored procedure
  • Stored Procedure Versioning
  • SQL Server Database to share with team?
  • How to version control SQL Server databases?
  • Is RedGate SQL Source Control for me?
  • What would be the best way to transfer my (small) SQL Server database between 2 locations?
  • Manage project backend and frontend in two separate branches or repositories?
  • Public and private code in a single Git repository
  • TFS Structure - Multiple Projects or Single Project?
  • Why Kiln is based on Mercurial, and not other (D)VCS
  • How to properly use git and branches
  • Best way of creating lite and extended version of Git project
  • 6 Solutions collect form web for “How to add Stored Procedures to Version Control”

    Background: I develop a system that has almost 2000 stored procedures.

    The critical thing I have found is to treat the database as an application. You would never open an EXE with a hex editor directly and edit it. The same with a database; just because you can edit the stored procedures from the database does not mean you should.

    Treat the copy of the stored procedure in source control as the current version. It is your source code. Check it out, edit it, test it, install it, and check it back in. The next time it has to be changed, follow the same procedure. Just as an application requires a build and deploy process, so should the stored procedures.

    The code below is a good stored procedure template for this process. It handles both cases of an update (ALTER) or new install (CREATE).

    IF EXISTS(SELECT name
                FROM sysobjects
               WHERE name = 'MyProc' AND type = 'P' AND uid = '1')
       DROP PROCEDURE dbo.MyProc
    GO
    
    CREATE PROCEDURE dbo.MyProc
    AS
    
    GO
    

    However following sample is better in situations where you control access to the stored procedures. The DROPCREATE method loses GRANT information.

    IF NOT EXISTS(SELECT name
                FROM sysobjects
               WHERE name = 'MyProc' AND type = 'P' AND uid = '1')
       CREATE PROCEDURE dbo.MyProc
       AS
       PRINT 'No Op'
    GO
    
    ALTER PROCEDURE dbo.MyProc
    AS
    
    GO
    

    In addition, creating a process to build the database completely from source control can help in keeping things controlled.

    Create a new database from source control.
    Use a tool like Red Gate SQL Compare to compare the two databases and identify differences.
    Reconcile the differences.

    A cheaper solution is to simply use the “Script As” functionality of SQL Management Studio and do a text compare. However, this method is real sensitive to the exact method SSMS uses to format the extracted SQL.

    I’d definitely recommend some third party tool that integrates into SSMS. Apart from SQL Source Control mentioned above you can also try SQL Version from Apex.

    Important thing is to make this really easy for developers if you want them to use it and the best way is to use tool that integrates into SSMS.

    I think it’s good to have each stored procedure scripted to a separate .sql file and then just commit those files into source control. Any time a sproc is changed, update the creation script – this gives you full version history on a sproc by sproc basis.

    There are SQL Server source control tools that hook into SSMS, but I think they are just scripting the db objects and committing those scripts. Red Gate looks to be due to releasing such a tool this year for example.

    We just add the CREATE statement to source control in a .sql file, e.g.:

    -- p_my_sp.sql
    CREATE PROCEDURE p_my_sp
    AS
        -- Procedure
    

    Make sure that you only put one SP per file, and that the filename exactly matches the procedure name (it makes things so much easier to find the procedure in source control)

    You then just need to be disciplined about not applying a stored procedure to your database that hasn’t come from source control.

    An alternative would be to save the SP as an ALTER statement instead – this has the advantage of making it easier to update an existing database, but means you need to do some tweaking to create a new empty database.

    I’ve been working on this tool http://timabell.github.com/sqlHawk/ for exactly that purpose.

    The way to ensure no-one forgets to check in their updated .sql files is by making your build server force the staging and live environments to match source control 😉 (which this tool will assist you with).

    2nd solution from @Darryl didn’t work as suggested by @Moe. I modified @Darryl’s template and I got it working, and thought it would be nice to share it with everybody.

    IF NOT EXISTS(SELECT name FROM sysobjects
                  WHERE name = '<Stored Proc Name>' AND type = 'P' AND uid = '1')   
        EXEC sp_executesql N'CREATE PROCEDURE dbo.<Stored Proc Name>
        AS
        BEGIN
            select ''Not Implemented''
        END
        '
    GO
    
    ALTER PROCEDURE dbo.<Stored Proc Name>
    AS  
    BEGIN
      --Stored Procedure Code 
    End
    

    This is really nice because I don’t lose my stored procedure permissions.

    Git Baby is a git and github fan, let's start git clone.