tsql - The best practice for creating a stored procedure in sql -

i had problem once, creating procedure , when want alter used drop first re-create again. thought taking lot of time. had find better way!

which best way, ease time. , incase need alter later??

its interesting found can using 1 procedure. divide procedure 2 parts:

• first part creates empty/dummy stored procedure/stub if stored procedure specified name in specified schema not exist. useful initial setup, when creating stored procedure in new environment.

• the second part of above script, alters stored procedure – whether it’s created in first step or existed before. so, every time need make changes in stored procedure, change alter procedure section (second part) of above script , entire script can executed without worrying whether stored procedure exists or not.

sample code:

use adventureworks  go  if object_id('dbo.uspgetemployeedetails') null -- check if sp exists     exec('create procedure dbo.uspgetemployeedetails set nocount on;') -- create dummy/empty sp   go  alter procedure dbo.uspgetemployeedetails -- alter sp      @employeeid int   begin      set nocount on;       select  hre.employeeid ,              pc.firstname + ' ' + pc.lastname employeename ,              hre.title employeetitle ,              pc.emailaddress employeeemail ,              pc.phone employeephone         humanresources.employee hre              left join person.contact pc                  on hre.contactid = pc.contactid      hre.employeeid = @employeeid   end  go 

hope helps someone, thanks.

here link more information: http://dattatreysindol.com/2012/05/29/tips-n-tricks-t-sql-an-elegant-way-to-create-or-alter-stored-procedures-in-one-go-for-easy-maintenance/