Post reply

Alter multiple procedures within the same script wrapped in transaction

  • June 12, 2023 at 6:14 am

    #4209315

    Hi

    I am looking for a way to alter multiple procedures in the same script wrapped in a transaction.

    Begin Transaction

    alter proc......

    alter proc.....

    end transaction

    This doesn't work, is there a way of doing it without using dynamic sql?

    The purpose of this is for promoting code from Dev to UAT and if there are errors in the code or it doesnt run successfully it can be rolled back.

    Thanks in advance

  • June 12, 2023 at 6:49 am

    #4209336

    What error are you receiving?

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • June 12, 2023 at 6:54 am

    #4209339

    Create/alter procedure should be the first statement in a query batch

     

    thanks for the reply

  • June 12, 2023 at 7:58 am

    #4209356

    Deploying using SSDT takes care of this for you. Have you considered using that?

    Another possibility would be to snapshot your UAT database prior to deploying and do a revert in case of error.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • June 12, 2023 at 8:06 am

    #4209380

    I haven't tried SSDT, id like to though

    its completely new to me so I'm not sure how it could help, could you give me a start please

  • June 12, 2023 at 8:13 am

    #4209382

    That is rather a large topic! But SSDT allows you to create a 'project' for each of your databases. These projects are contained in one or more 'solutions'.

    Each project contains all of the DDL files needed to recreate the database – essentially a single .SQL file for each database object.

    Rather than modify database objects directly in SSMS, you use VS (or, more accurately, SSDT (SQL Server Data Tools)) to modify the .SQL files and then you can run a 'deploy'. The deployment process does a schema-compare between your target database and your database project. If any differences are found, the deployment makes modifications to the target DB to bring it in line with what is contained in your database project.

    Database projects are also great for source control.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • June 12, 2023 at 8:28 am

    #4209384

    The current process is, the devs dev 🙂 and then once finished they create a script and send it to the dbas who run that script in management studio

    I dont think that SSDT could help in SSMS could it?

  • June 12, 2023 at 8:50 am

    #4209400

    I used to work that way about 15 years ago.

    Here is a sample process developing using SSDT. It assumes that devs do development locally.

    1. Dev checks out a new Git branch
    2. Dev develops changes locally in SSDT, including the testing of deployment to their local DB.
    3. When changes are complete, the dev checks in and pushes their code changes to a central repo and creates a pull request (PR) for review.
    4. A senior developer opens the PR and assesses the changes. If any code needs to be tweaked, the senior dev tells the dev. The dev makes changes, checks in and pushes the changes once more. The senior dev reviews again.
    5. When the senior dev is happy with the changes, they 'complete' the PR to merge the changes into your UAT branch.
    6. Using a tool such as TeamCity, Octopus Deploy or Azure DevOps, the changes to the branch are detected and deployed automatically.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • June 12, 2023 at 9:06 am

    #4209401

    thanks a lot for the reply, I know its not ideal not having everything in source control, its is a task on the list I am told

    another question

    Does the "Pull Request" include multiple procedures if needed?

    so as far as I know a single repository is used for a single procedure/view/function/etc, and code is checked in and checked out of repositories whilst devs are deving (is that right?).

    but if you want to promote to UAT you may want to use multiple repositories and maybe a few data manipulation commands and if you didn't run them in order there would be errors. So they required deployment might be

    1. Repository Proc A
    2. Repository Function B
    3. Update this
    4. Insert into that

    Then would that as a whole be a pull request?

  • June 12, 2023 at 9:19 am

    #4209404

    A source control repository contains a (technical term) bunch of files which form some sort of logical grouping. That grouping is your choice.

    An example repo would be 'Databases'. This could contain the database projects for all of your User databases.

    If the dev makes changes to tables, views, functions and procs as part of a change, all of these changes are included in the PR.

    When SSDT deploys the changes, it takes care of ordering.

    Things get interesting where data needs to be manipulated during deployment. There are things known as pre- and post-deployment scripts that can usually handle this, with some crafting.

    As I suggested earlier, there is a lot to learn. If you are considering implementing this, I suggest you start with a simple DB that is not mission-critical, so that you can get used to a different way of working.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • June 12, 2023 at 9:59 am

    #4209434

    Thanks again, I'm going to start today 🙂

  • July 20, 2023 at 8:20 pm

    This was removed by the editor as SPAM

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply