• Replies 4 replies
  • Subscribers 702 subscribers
  • Views 84 views
  • Users 0 members are here
Options
Related

modify a stored procedure

Lesley Chaney
$organization

Hi all,

We need to modify a stored procedure. Once we've made the change that we want, how do we "save" that change? Execute? Or some other option?

(I feel execute is the right answer, but asking for a colleague, as I personally have NO business modifying procedures!)

Thanks smart friends!

Parents
  • $organization

    I suggest the following steps/ process: 

    1. Save a backup of the current script (This is the most important step).  In SSMS, right click on the procedure and "script stored procedure as CREATE".  Please do not just click "Alter" from here as tempting as that might be.  Save this script somewhere.  I like "CREATE stored_procedure_name.sql"

    2. Make the modification to the code and ADD COMMENTS to the SQL and test!  - Depending on the change, this testing may be more simple or more complex. Change the word "Create" to "Alter" in the create script and save it again  as "ALTER stored_procedure_name YYYYMMDD.sql".  (You can also use the syntax Create or Alter, but I'm keeping this simple).  

    3. Alter the procedure. On the DB by clicking "execute" on the Alter script.  This is different than executing the stored procedure. 

    4. Test again by executing the stored procedure.  This sytnax is 

    execute stored_procedure_name @param1=XXXX, @param2=XXXX

Reply
  • $organization

    I suggest the following steps/ process: 

    1. Save a backup of the current script (This is the most important step).  In SSMS, right click on the procedure and "script stored procedure as CREATE".  Please do not just click "Alter" from here as tempting as that might be.  Save this script somewhere.  I like "CREATE stored_procedure_name.sql"

    2. Make the modification to the code and ADD COMMENTS to the SQL and test!  - Depending on the change, this testing may be more simple or more complex. Change the word "Create" to "Alter" in the create script and save it again  as "ALTER stored_procedure_name YYYYMMDD.sql".  (You can also use the syntax Create or Alter, but I'm keeping this simple).  

    3. Alter the procedure. On the DB by clicking "execute" on the Alter script.  This is different than executing the stored procedure. 

    4. Test again by executing the stored procedure.  This sytnax is 

    execute stored_procedure_name @param1=XXXX, @param2=XXXX

Children
No Data