none
Creating multiple stored procedures in one go.

    Pregunta

  •  

    I have 50-60 stored procedures in my application and these are stored in different files as per file one stored procedudre.

     

    I want to deploy my applicaiton. I want to create a batch or Single SQL file to create all stored precodures in on go.  

     

    My objective is to ensure that No stored procedure has left while deploying the applicaiton.

     

    thanks.

    lunes, 25 de febrero de 2008 6:17

Respuestas

  • Regardless of how you may consolidate all the different files into one single file with all procs, it's important that the original files ends with a GO, or that you by some other means add a GO between each 'small file'.

     

    So that you end up something like this;

     

    create proc1

    .....

    GO

    create proc2

    ...

    GO

     

    and so on.

     

    The reason being that a 'create procedure' must be the first statement in a batch, else you'll get errors when you try to run the whole lot on top of eachother.

     

    /Kenneth

     

    lunes, 25 de febrero de 2008 7:58

Todas las respuestas

  • One option is to put all the script to a single .sql file and run it on the server. You can do this by running the DOS command as follows:

     

    Code Snippet

    COPY *.sql AllProcedures.sql

     

     

    After you run this command (assuming that all the .SQL files are in a single folder) you will get a new file named "allprocedures.sql" which contains the definition of all the stored procedures (you had in the folder).

     

    You can run this code on your server. probably you can use osql.exe and pass the filename as the argument or run it from management studio. This article might help understand osql.exe: http://www.databasejournal.com/features/mssql/article.php/3403331

     

    lunes, 25 de febrero de 2008 6:43
  • Regardless of how you may consolidate all the different files into one single file with all procs, it's important that the original files ends with a GO, or that you by some other means add a GO between each 'small file'.

     

    So that you end up something like this;

     

    create proc1

    .....

    GO

    create proc2

    ...

    GO

     

    and so on.

     

    The reason being that a 'create procedure' must be the first statement in a batch, else you'll get errors when you try to run the whole lot on top of eachother.

     

    /Kenneth

     

    lunes, 25 de febrero de 2008 7:58