Post reply

Coding convention for writing a stored procedure script (ALTER or DROP-CREATE)

  • January 5, 2008 at 9:54 am

    #180228

    I'm writing a set of stored procedures for a new project and I cannot decide between the following two coding conventions for writing a new stored procedure:

    Option 1:

    IF EXISTS(SELECT name FROM sysobjects WHERE name = ' ' AND type = 'P')

    DROP PROCEDURE

    GO

    CREATE PROCEDURE

    This is nice and I can easily create my sprocs, modify them, post them to my hosting/production DB server, etc. Only thing is I'm repeating the proc_name 3 times in the script.

    Option 2:

    CREATE PROCEDURE , execute it, then change script to:

    ALTER PROCEDURE

    This is more simple code to write for each sproc, but whenever I need to post the sproc to the production server, I need to change back each script from ALTER to CREATE, execute it there and change back the script to ALTER for future modifications. It would be really nice if there were a CREATEorALTER statement available in T-SQL.

  • January 5, 2008 at 4:49 pm

    #765106

    In SQL Server 2000, there is no "Modified Date" on a stored procedure. So I always drop the procedure and recreate it so the Create Date shows the latest rev...

    ... but I don't use all that IF Exists stuff to check for existance... I use the functions that are native to SQL Server...

    IF OBJECT_ID('dbo.yourprocname','P') IS NOT NULL

    DROP PROCEDURE dbo.yourprocname

    CREATE PROCEDURE dbo.yourprocname ...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 5, 2008 at 8:12 pm

    #765117

    Thanks for the reply.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply