SP log - code reuse + maintenance tips 1

[LNBruno]

Hi, all.

I'm setting up a table in our database to store pertinent data concerning stored procedures to aid with code reuse during development and to assist in future maintenance.

Here's the table structure:

CREATE TABLE dbo.SPlog
    (
    pkID            int           IDENTITY
    , ProcedureName varchar(100)  NOT NULL -- using TableNameOrBusinessComponent_Action naming convention
    , Purpose       varchar(500)  NOT NULL -- short description of what it does
    , InputParams   varchar(1000) NOT NULL -- what goes in
    , OutputParams  varchar(1000) NOT NULL -- what comes out
    , Details       varchar(1000) NOT NULL -- additional info
    , CalledSPs     varchar(1000) NOT NULL -- other procedures used by this one
    , UsedBySPs     varchar(1000) NOT NULL -- other procedures that use this one
    , CreatedBy     varchar(50)   NOT NULL -- original developer name
    , Created       smalldatetime NOT NULL -- use GETDATE() for insert
    , CurrentRevBy  varchar(50)   NULL     -- latest reviser's name
    , CurrentRev    smalldatetime NULL     -- for updates; still use GETDATE()
    , CONSTRAINT PK_SPlog PRIMARY KEY (pkID)
    )
GO

An example record:

    ProcedureName = TableNamePrimaryKey_VAL
    Purpose       = checks to see if a primary key value exists on a table
    InputParams   = @intPKid - int
    OutputParams  = @bitExists - bit
    Details       = this is a bad example
    CalledSPs     = none
    UsedBySPs     = TableName_GET, TableName_SEL, TableName_UPD, TableName_DEL
    CreatedBy     = LNBruno
    Created       = Mar  2 2007  8:06AM
    CurrentRevBy  = NULL
    CurrentRev    = NULL

Questions: what would you add to this? how could it be improved?

Thanks, & Happy Friday!

< M!ke >

 

[philhege]

Include user-defined fields and functions used by the SP, and separate the extended procedure calls into another category.

Add another table to log revision comments, e.g. "removed the call to that evil Goldmine extended procedure".

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[LNBruno]

Thanks, Phil. I was thinking of having another table similiar for user-defined functions; didn't think about the relationship between sp's & them. Good call!

We're using a standard comment block at the top of each sp to list change history, but since we're going to this trouble, we might as well add a revision comments table to provide the same searchability.

< M!ke >

 

[philhege]

Right; I have a standard template that displays all of that stuff at the top of the SP. It'd be nice to have it in a DB.

I started writing a "new SP" app where the developer has to open the app and fill out the meta information fields. The app would give the dev a .sql file with the bits filled in and the framework (adds/drops, server settings, base format, etc.) already in place.

Maybe I'll write that when I get done with my other 25 enterprise-priority projects ;^)

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[SQLSister]

I would at a minumum make related tables for parameters, and calledby and used by. Since there will be multiple values in each of these then it is better to store them in related tables to make it easier to search.

Also, I know people like the idea of reusing code, but honestly you have to be extremely careful when you do that as it is a quick way to an inefficient and/or insecure system using dynamic SQL or asking for more columns than you need (sometimes from more tables than you need) in order to suit multiple purposes. Code reuse is a bad idea when it impacts system efficiency. At least you are documenting the reuse, so you will have a way to easily see what will be impacted by a change. That is another problem with code reuse - it gets changed for one business purpose and promptly breaks somethign else that needed the column you took out or was expecint to have five columns ina specific order and now gets 6 columns in a different order.

Questions about posting. See faq183-874

 

[philhege]

I see your point about that, SQLSister; stored procedures work best when designed specifically to a purpose.

I'm more concerned with documentation, categorization, and coding standards than I am with reuse, although Mike may disagree. When the procedure count starts climbing north of 1000 or so, it's nice to have a meta index. "Oh, what was that xxxsp_GetSomeSuchData all about?" "Where can I get a time-ordered list of filaraptic grommets from the inventory table?"

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[LNBruno]

Great idea, Phil. Call me when you get that "new SP" app done - that is if MS doesn't steal your idea and implement it in the next release. ;-)

SQLSister - I can understand the calledby and usedby being off in their own table (same names used repeatedly), but not sure about the param lists. We have coding standards in place, but nothing says developers can't make up any name they want to for a param to represent the same data element (e.g., @intPKid, @PrimaryKey, etc.). Yeah, I'm not crazy about that either, but I'm just a lowly code-monkey. That said, I figured if we at least included them, we'd have an "at a glance" of what the i/o params were without having to crack open the actual sp.

Your point is well taken with the caution of reusing code. We're looking to use this in identifying candidates for reuse, with the full impact being discussed with the dev team, and proceeding only after consensus that it makes sense.

And you caught me: the reason for the calledby & usedby was to minimize "hijacking" code.

Rule One: "Do no harm."

Rule Two: "It's harder than you think."

;-)

< M!ke >

 

[LNBruno]

I'm more concerned with documentation, categorization, and coding standards...

Right...and while the discussion is leaning more toward the reuse side, I'm also thinking about maintenance as SQLSister's pointing out:

"If I pull this string, is a bell going to ring in a different room?"

< M!ke >

 

[philhege]

Even parameters have naming conventions. Really, any publicly-exposed variable should be governed by a sensible, consistent naming policy. It's just plain crazy to let 'em run wild with something like that.

We're struggling with much the same thing but are reining in the strays. It's been a good exercise: each of us was given the responsibility to research and define coding standards in an area out of our comfort zone. I'm the T-SQL guru, and I had to come up with .NET coding standards. Nothing like unfamiliarity to help you focus.

Anyhoo, good luck, and, SQLSister, I'll try to see if I can beat Bill to the punch. Probably not.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.