locked
A Stored Procedure to Create a Set of Stored Procedures RRS feed

  • Question

  • Is it possible to use a stored procedure to create a bunch of other stored procedures?  When the user creates a new database, I need to create a bunch of stored procedures and I would like to just run a stored procedure on the new database that creates all the needed stored procedures.  I tried just putting the CREATE PROCEDURE syntax inside a stored procedure and got some errors.  I imagine this can be done using dynamic sql and the EXECUTE function but I was hoping there was a better way.

    Thanks.

    Nick


    Nick's Programming Tips
    Tuesday, October 19, 2010 2:52 PM

Answers

  • If the procedures are the same for every database why not just put them in the 'model' database and the create database statement will copy them to the new database.
    Tom G.
    • Proposed as answer by Naomi N Tuesday, October 19, 2010 3:08 PM
    • Marked as answer by Nick Olsen Tuesday, October 19, 2010 3:10 PM
    Tuesday, October 19, 2010 2:59 PM

All replies

  • I am also doing same kind of job through the dynamic sql
    " Education is the beginning of transformation. Dedicate yourself to daily learning via Blogs/Forums/books and coaching "
    Click here to read my blog
    Tuesday, October 19, 2010 2:56 PM
  • If the procedures are the same for every database why not just put them in the 'model' database and the create database statement will copy them to the new database.
    Tom G.
    • Proposed as answer by Naomi N Tuesday, October 19, 2010 3:08 PM
    • Marked as answer by Nick Olsen Tuesday, October 19, 2010 3:10 PM
    Tuesday, October 19, 2010 2:59 PM
  • Perfect.  I was not aware of the purpose of the model database.  Thanks for the info.
    Nick's Programming Tips
    Tuesday, October 19, 2010 3:11 PM