The best practice for creating a stored procedure in sql

Question

I had this problem once, creating a procedure and then when I want to alter it I used to drop it first then re-create it again. Then I thought that was taking a lot of time. I had to find a better way!

which is the best way, to ease the time. and also incase you need to Alter it later??

Answer 1

Its interesting I found that you can do all this using one procedure. you divide the procedure to two parts:

• first part creates an empty/dummy stored procedure/stub if the stored procedure with the specified name in the specified schema does not exist. This is useful for the initial setup, when you are creating this Stored Procedure in a new environment.

• The second part of the above script, alters the stored procedure always – whether it’s created in the first step or it existed before. So, every time you need to make some changes in the stored procedure, only change the ALTER PROCEDURE section (second part) of the above script and entire script can be executed without worrying whether the stored procedure already exists or not.

Sample code:

USE AdventureWorks 
GO 
IF OBJECT_ID('dbo.uspGetEmployeeDetails') IS NULL -- Check if SP Exists
    EXEC('CREATE PROCEDURE dbo.uspGetEmployeeDetails AS SET NOCOUNT ON;') -- Create dummy/empty SP 

GO 
ALTER PROCEDURE dbo.uspGetEmployeeDetails -- Alter the SP Always 
    @EmployeeID INT 
AS 
BEGIN 
    SET NOCOUNT ON; 

    SELECT  HRE.EmployeeID , 
            PC.FirstName + ' ' + PC.LastName AS EmployeeName , 
            HRE.Title AS EmployeeTitle , 
            PC.EmailAddress AS EmployeeEmail , 
            PC.Phone AS EmployeePhone 
    FROM    HumanResources.Employee AS HRE 
            LEFT JOIN Person.Contact AS PC 
                ON HRE.ContactID = PC.ContactID 
    WHERE HRE.EmployeeID = @EmployeeID 

END 
GO

Hope it helps someone, Thanks.

Here is a link for 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/