Alter or Create multiply stored procedures at once from multiply files in SQL Server 2008 -

i have large amount of stored procedures updating , transferring duplicate database on server. have been opening each “storedproc.sql” file within sql server management studio 2008 , selecting execute in tool bar ether create or alter existing stored procedure. have been doing each stored procedure.

i looking script (or way) allow me alter of stored procedures on databases ones located in folder @ 1 time. looking script similar pseudo-code text below.

use [databasename] update [storedprocname]    using [directory\file\path\filename.sql] update [storedprocnamen]    using [directory\file\path\filenamen.sql … 

not cleanest pseudo-code understand idea. willing drop of stored procedures (based on name) , create same stored procedures again on database. if need more clarity don’t hesitate comment, thank in advance.

to further explain:

i changing every reporting stored procedure ssrs conversion project. once report developed, move report , stored procedure server. have manually run (alter or create) each stored procedure against duplicated database database able support report on server. far has not been trouble, have 65 85 stored procedures; , if have add 1 dataset field each one, have run each 1 manually update duplicate database.

what want able have sql script says: database, alter/create named stored procedure , can find .sql text file details in folder.

here code use move stored procedures 1 database another:

declare @spbody nvarchar(max); declare @spname nvarchar(4000); declare @spcursor cursor; set @spcursor = cursor     select ao.name, sm.definition     <source database>.sys.all_objects ao join          <source database>.sys.sql_modules sm          on sm.object_id = ao.object_id     ao.type = 'p' , schema_name(ao.schema_id) = 'dbo'     order 1;  open @spcursor;  fetch next @spcursor @spname, @spbody; while @@fetch_status = 0 begin     if exists(select * <destination database>.information_schema.routines r r.routine_name = @spname)     begin         set @query = n'drop procedure '+@spname;         exec <destination database>..sp_executesql @query;     end;     begin try         exec <destination database>..sp_executesql @spbody;     end try     begin catch         select @errmsg = 'error creating '+@spname+': "'+error_message()+'" ('+@spbody+')';         --exec sp__loginfo @procname, @errmsg, @productionrunid;     end catch;     fetch next @spcursor @spname, @spbody; end; 

you need put in , appropriate.