Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2005
    Posts
    319

    Unanswered: storing Stored Procedures in version control - ALTER or DROP / CREATE?

    I was told a few years ago that we should always store our scripts as DROP / CREATE, I forget the exact reason, query plan, recompilation issues?

    Anyway I just downloaded Red-Gate's version control integration product and it stores all changes to procs as Alter's (and deploys as Alter's).

    Is this ok, or should I forgo using this product and stick to the IF Exists (...) Drop ... GO Create ... ?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There were once good reasons for avoiding ALTER PROCEDURE, just like there were once good reasons for avoiding monotonically incrementing integers (the IDENTITY property). The reasons for avoiding them both have passed, you can safely use either today.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jun 2005
    Posts
    319
    cool, that's what I had hoped.

    thread hijack:

    Anyone ever use MSFT Team Foundation Server? We have the option to integrate directly w/ that instead of svn w/ the Red-Gate Source Control product. Wondering if it is worth the extra effort? I always wanted to use TFS, just not sure what I am getting myself into.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Team Foundation Server is a lovely thing, IF everyone commits to using both TFS and Visual Studio.

    It makes life lots easier in many ways. Every version of everything is captured, and with just a bit of luck the changes are relatively well documented, and often tied to a deliverable of some kind. This makes it far easier to manage the code, the people (developers, DBAs, admins, etc), and the process. Programming in the VS/TFS environment is gloriously liberating to the folks that want to get things done but you'll see some real resistance from the folks that like to "nurture secrets". At least from the TFS admin's point of view, there are no secrets in the project!

    I like working with TFS, lots!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Jun 2005
    Posts
    319
    Cool, here is the product from Red-Gate that integrates it into SSMS, I assume no need to use VS in this case, right?

    Source Control your SQL Server database in Subversion or Team Foundation Server

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm not familiar with the Red Gate tool, but I'm pretty sure that it works directly with SSMS.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I saw a presentation of a pre-release version of the Red Gate source control tool. It looked excellent to my mind. I haven't used TFS though so can't compare.

    As an aside, I also use "create if not exists" then "alter" syntax for my sprocs. Main reason is to retain the original creation date, but it is a pretty minor reason really.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jun 2005
    Posts
    319
    This is a pretty cool implementation as well

    Code:
    IF OBJECT_ID(N'MyProc', N'P') IS NULL
    EXEC ('CREATE PROC MyProc AS SELECT 1;');
    
    GO
    
    ALTER PROCEDURE	MyProc
    AS
    SELECT 2;
    
    GO
    essentially create the stub if it does not exist and then have the alter after it

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yes, that's exactly how I do it.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •