title: Alter or Create multiply stored procedures at once from multiply files in SQL Server 2008
body: <p>I have a large amount of stored procedures that I am updating often and then transferring to a duplicate database on another server. I have been opening each “storedproc.sql” file from within SQL Server Management Studio 2008 and then selecting Execute in the tool bar which will ether create or alter an existing stored procedure. I have been doing this for each stored procedure.</p>
  
  <p>I am looking for a script (or another way) that will allow me to alter all of the stored procedures on the databases with ones that are located in a folder at one time. I am basically looking for a script that will do something similar to the pseudo-code like text below.</p>
  
  <pre><code>USE [DatabaseName]
  UPDATE [StoredProcName]
     USING [directory\file\path\fileName.sql]
  UPDATE [StoredProcNameN]
     USING [directory\file\path\fileNameN.sql
  …
  </code></pre>
  
  <p>Not the cleanest pseudo-code but hopefully you understand the idea. I would even be willing to drop all of the stored procedures (based on name) and then create the same stored procedures again on the database. If you need more clarity don’t hesitate to comment, I thank you in advance.</p>
  
  <p>To further explain:</p>
  
  <p>I am changing every reporting stored procedure for an SSRS conversion project. Once the report is developed, I move the report and the stored procedure to a server. I then have to manually run (ALTER or CREATE) each stored procedure against the duplicated database so the database will now be able to support the report on the server. So far this has not been too much trouble, but I will eventually have 65 to 85 stored procedures; and if I have to add one dataset field to each one, then I will have to run each one manually to update the duplicate database.</p>
  
  <p>What I want to be able to do is have a SQL script that says: For this database, ALTER/CREATE this named stored procedure and you can find that .sql text file with the details in this folder.</p>