dcsimg
Results 1 to 9 of 9

Thread: Stored Procedures

  1. #1
    Join Date
    Dec 2006
    Location
    Portland, Oregon
    Posts
    64

    Question Stored Procedures

    This thread is related to the second article in the SqlCredit series: Part 2: Creating the Database, Tables, CRUD Procedures.

    What do you like or dislike about the stored procedures in the attached code?

    Remember that these procedures have to support both SQL Server 2000 and 2005.

    Rob
    Last edited by rgarrison; 02-23-2007 at 01:02 PM. Reason: Updated after article published.

  2. #2
    Join Date
    Dec 2006
    Location
    Portland, Oregon
    Posts
    64

    SP naming convention

    I had an internal e-mail conversation with Greg Grater, one of the database experts here at Corillian. It is copied below with just a few changes to hide details of customers and projects. It reads top-to-bottom.

    Greg
    I have some opinions around the CRUD stored procedures and the naming of those stored procs.

    In my databases, I like to follow this standard…

    <prefix>_<Object>_<action>

    Where action might be something like

    • ReadByCardnumber
    • ReadByAccountID
    • ReadByAccountNumber
    • DeleteByAccountID
    • PurgeByDate
    • PurgeByStatus
    • Etc…

    What do you normally do and why?

    Rob
    We used to always use the prefix (“CCSP_”, “CCSPAM_”, etc.). We dropped that because people didn’t think it was needed. I’m fine with leaving it off.

    If we consolidated databases (and used 2005), we would likely use different schema to separate objects.

    I have used <action> before <object> just because that was how I started with a standard. I actually like the idea of using the <object> first because of sorting. The only issue I see here is reads. If you have a read proc that reads from obj1, obj2, and obj3, what do you call it?

    Greg
    In “most” cases a read from multiple objects has a primary purpose and thus a parent object that makes it clear. For instance, I have several tables that are involved in the XX_CardTransactions_ReadByTransactionID, but the net result of the read is to list card transactions and their children’s data.

    The sorting of objects is paramount in my opinion. Although SQL 2005 schemas provide some sorting and grouping, our products are vast and complex. By using the object name first, we easily avoid some duplication and make it easier to maintain architecture changes down the road. For instance: We had a table called ApplicationNotes that contained notes for a user’s application. Later down the road, a new requirement to capture the same kind of notes for App2 bubbled up. The knee jerk reaction was to create a second table called App2Notes. But, because the stored procedures were sorted/grouped in the manner described below, we were able to quickly assess that we could change the table name to Notes add a type column and quickly restructure the associated stored procedures into the following:

    • XX_Notes_Create
    • XX_Notes_CreateApp2Note
    • XX_Notes_CreateApplicationNote
    • XX_Notes_ReadApp2NotesByThisID
    • XX_Notes_ReadApp2NotesByThatID
    • XX_Notes_ReadApplicationNotesByThisID
    • XX_Notes_ReadApplicationNotesByThatID
    • XX_Notes_ReadByThisID
    • XX_Notes_ReadByThatID

    Having the object name as the primary sort criteria has been extremely valuable for maintaining the structure, but also for the developers to understand the structure and what is available. They can quickly review the stored procedures and reverse engineer most of the functionality available to the object simply because they only have one place to look in the list of stored procs.

    Rob
    Great ideas.
    Last edited by rgarrison; 02-23-2007 at 03:02 PM. Reason: Minor changes requested by Greg

  3. #3
    Join Date
    Feb 2007
    Posts
    5
    Personally, I love the ObjectVerbModifiers naming convention. The sorting obviously makes it much easier to find stuff and to prevent creating redudant sprocs. It also seems to lead to more predictable names so that people don't even have to look up the list. I suspect that's because the VerbObject style is more like English, which is more loose and unpredictable.

    I've used this convention on a few recent projects and found it to be a joy, both from a DB engineer perspective and that of a middle tier developer.

    However, the main downside is that web services more often use the VerbObject convention. Can you have your web service interface use one convention and your sprocs another? Of course. But if you're using a data access layer (DAL) that provides more automated support when your operations and sprocs are using the same convention, you'd have to decide if the convention is worth it. Better yet, you could just modify your DAL to automate things either way.

  4. #4
    Join Date
    Mar 2007
    Posts
    4

    Functionality

    I highly recommend a object naming convention of [Object][Action][Modifier]. I go one step further and recommend staying with the CRUD Actions -instead of SELECT, GET, FIND, etc., use READ. (Some sprocs may have non-CRUD actions such as CALCULATE.) A large DEV team on a large project can be very creative in naming. I want to be able to minimize duplication of effort, and ease of locating (and reusing) similar existing code should be the primary focus of how names are created.

    Also, what value is added by a [PREFIX]. A lot of folks use sp, usp, etc. (hopefully, not many use [sp_]), and what is the added value? You know that it's a sproc by context. You are needlessly wasting keystrokes. However, sometimes it is necessary to create a sproc in dbA that runs against data in dbB, and in those situations, I recommend using the db name (or code) as a prefix.

  5. #5
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    After a quick glance at the scripts, this is what I found.

    CreateDatabase.sql - Everything is harcoded. Should be more flexible. My Company name could be SQLCredit but my database could be CreditTracker.

    StoredProcs.sql - Should use Try Catch, COmmit and rollback based on the errorstatus.

    Naming Convention is ok as long as it is consistent.

    Required data dictionary and more documentation on the procedures.

    Suggestion on DBDesign/performance related: Would recommend creating primary key on all tables - easy for replication in future. Clustered index on numeric column,
    Avoid using identity - difficult to replicate if we are going to use replicated DB as DR.

  6. #6
    Join Date
    Dec 2006
    Location
    Portland, Oregon
    Posts
    64
    MAK, thanks for the feedback. A few comments here:
    Quote Originally Posted by MAK
    CreateDatabase.sql - Everything is harcoded. Should be more flexible. My Company name could be SQLCredit but my database could be CreditTracker.
    Making the database name configurable can be done if it's needed. In my opinion this is YAGNI.

    It's a trade-off. As soon as you make it configurable, you have to pass it around from script to script. You can't just do something simple in a script like 'USE SqlCredit;'.

    The location of the data and log files should be configurable, but it complicates the code, so I left it out. For a product, you would need to make that configurable. I will add it to the backlog.
    Quote Originally Posted by MAK
    StoredProcs.sql - Should use Try Catch, Commit and rollback based on the errorstatus.
    The whole thing is built to support both SQL 2000 and 2005 for now, so I'm not using TRY/CATCH. In these simple INSERT/UPDATE/DELETE statements, there is no need to wrap the statement in an explicit BEGIN/ROLLBACK/COMMIT. For procedures that include multiple actions, I will definitely use explicit transactions.
    Quote Originally Posted by MAK
    Required data dictionary and more documentation on the procedures.
    Yes, I failed to include an E/R diagram in the second article. That will be in the third. As the schema grows and changes, I will include updated diagrams.

    I think stored procedure documentation will create a lot of discussion, so I started a separate thread.
    Quote Originally Posted by MAK
    Suggestion on DBDesign/performance related: Would recommend creating primary key on all tables - easy for replication in future. Clustered index on numeric column,
    Avoid using identity - difficult to replicate if we are going to use replicated DB as DR.
    These tables do have clustered primary keys that use IDENTITY. I'm not completely sure what you're suggesting, but I am intrigued.

    You're proposing two columns, one for clustering (int - populated how?) and one as the primary (non-clustered) key. I assume you're talking about a UNIQUEIDENTIFIER for the primary key using NEWID() or [if we were supporting only SQL 2005] NEWSEQUENTIALID().

    Do I have that right? It's an interesting option because it gets around the standard "don't use GUIDs as primary keys" argument because you're clustering on an [assumed ascending] integer.

    Can you elaborate on this?

    Thanks.

  7. #7
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    GUID is not necessary. Any insert or update or delete should be done thru procedures. You could have a table that maintains all the max value of each table and based on the max value you could add +1 before inserting and update the single table.

    In my experience, identity is a pain when it comes to transactional replication. Atleast in the next version, identity property would be replicable.

    ER diagram alone doesnt help in understanding the functionality. Data dictionary is also needed which explain the reason and function and relation of every column in the table. It also explains the functionality of the storedprocs and the parameters used. I would be glad to send a sample of database dictionary.

  8. #8
    Join Date
    Dec 2006
    Location
    Portland, Oregon
    Posts
    64
    Quote Originally Posted by MAK
    GUID is not necessary. Any insert or update or delete should be done thru procedures. You could have a table that maintains all the max value of each table and based on the max value you could add +1 before inserting and update the single table.
    This is an interesting option. We (Corillian) have done this in the past but moved in recent years to using IDENTITYs.

    An interesting point here is that we had people challenge us on the scalability of just the solution you mention. Short answer? We proved that it scales just fine.

    Quote Originally Posted by MAK
    In my experience, identity is a pain when it comes to transactional replication. Atleast in the next version, identity property would be replicable.
    This is a valid point. It leads many people to select NEWID() for their PKs, but that brings a bunch of other issues with it.

    Quote Originally Posted by MAK
    ER diagram alone doesn't help in understanding the functionality. Data dictionary is also needed which explain the reason and function and relation of every column in the table. It also explains the functionality of the storedprocs and the parameters used. I would be glad to send a sample of database dictionary.
    I have written data dictionaries and find them very valuable. There are a number of great tools available.

    To make one work requires a commitment by management that an updated data dictionary is a normal part of the deliverables for a project.

  9. #9
    Join Date
    Aug 2007
    Posts
    3
    I prefer ObjectAction naming, but as someone else mentioned, the real important thing is to have a standard and stick to it. A standard naming convention should be included for ALL objects.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •