modify a stored procedure

Lesley,

You could be interpreting the word "EXECUTE" with either of two slightly different meanings with stored procedures and SSMS in general, and, what with it being difficult to determine as to which you are referring above, I will try to clarify in whole below.

To modify a procedure, you just take the full "CREATE" statement, modify whatever portions of the procedure are going to be modified, and then just "run it" again, replacing the word "CREATE" with the word "ALTER".  By "run it", I am referring specifically to the word "Execute" in the SSMS console, which also is the thing with the green "play" button on it.  That version of the word "Execute" simply means that you are running the script in the current window.  Pressing the F5 key also accommodates this same result (so I generally use F5 as it is quicker for me).

In general, for procedures, the full CREATE statement creates the procedure.  An ALTER statement alters an existing procedure to that new logic.  If this is a new experience for someone relatively new to SQL, I would suggest finding the current, existing procedure in the SSMS object explorer and right click on it.  Then, choose "Script Stored Procedure as", then "CREATE To" and lastly "New Query Editor Window".  I like to do it that way in case I accidentally hit F5 while I am in the middle of working, as an existing procedure cannot be created AGAIN, so it will just return an error saying that "a procedure [with this name] already exists...".  Then, when I am finished making my updates, I go back up to the top and replace the word "CREATE" with the word "ALTER" and hit F5, or EXECUTE it.

The other way you might mean "execute" within SSMS is to EXECUTE an already existing procedure.  This essentially functions the same way as the F5/execute above except that it is code that is already being run as part of its own execution scheme.  That is, once I create/alter my procedure, I can run the logic EXECUTE [procedure I just updated].  That is what you would actually type into SSMS; so you are in actuality only running a window with one single line of code, your execute statement, however that line itself is executing the local procedure.  To EXECUTE in that way will take whatever logic is in that procedure and then runs it.  It is common for a newly altered procedure to be EXECUTED immediately thereafter with pre-chosen parameters so as to verify that the updates return the desired results, but there is no need to actually run the EXECUTE statement until that procedure next needs to be used.  The ALTER statement takes care of that all on its own.

Hope that clarifies, and best of luck!

John

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

I've shared your sage advice with our IT manager, and it's all on them from here!

Thank you all!