Home Forums Article Discussions Article Discussions by Author Discuss Content Posted by Steve Jones Building Better Stored Procedures RE: Building Better Stored Procedures

  • March 14, 2019 at 10:35 pm

    #2024528

    astruthers 75845 - Thursday, March 14, 2019 9:26 PM

    Jeff, 
    Just tested, and stand by what I said.
    -- Comments before Create\Alter statement
    CREATE PROCEDURE sp_Bletch
     @BrandID varchar(6)
    AS
    -- Comments After Create\Alter statement
     SELECT
      [ID]
      ,[Name]
      ,[BrandID]
     FROM [dbo].[BrandItems]
     WHERE [BrandID] = @BrandID;
    GO

    Then, if you Right-Click on object, and "Script Procedure As" the external comments are not included.

    You tested this in SSMS?  Interesting.  I also tested before I posted and it worked as I said both in 2008 SSMS against an SQL Server 2008 installation and SSMS revs 17.2 and 17.91 against a 2016 SQL Server installation... what version of SSMS are you using?

    Just in case there's a doubt, here's what I got when I did the very same thing you did to script the procedure back out...

    /****** Object: StoredProcedure [dbo].[uspLogError]  Script Date: 3/15/2019 12:28:54 AM ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    -- uspLogError logs error information in the ErrorLog table about the
    -- These notes will NOT get persisted with the object
    -- and will NOT be available to future developers scripting this object from SSMS
    CREATE PROCEDURE [dbo].[uspLogError]
    @ErrorLogID [int] = 0 OUTPUT -- contains the ErrorLogID of the row inserted
    AS -- by uspLogError in the ErrorLog table
    BEGIN
    SET NOCOUNT ON;
    Select 1;
    END
    GO

    The comments where saved as a part of the stored procedure.

    --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)