locked
SQL Server Stored Procedure Standards RRS feed

  • Question

  • Just wondering if there's any standards for writing and creating Microsoft SQL Server Stored Procedures.

    Like ";" after the entire SET or after each one...just things of that nature...or no use of ";"

    Thanks for your review and am hopeful for a reply.

    Tuesday, January 6, 2015 3:08 PM

Answers

  • Links for your reference

    http://blogs.msdn.com/b/cellfish/archive/2009/08/12/20-tips-to-write-a-good-stored-procedure-is-really-just-12.aspx

    http://sqlblog.com/blogs/aaron_bertrand/archive/2008/10/30/my-stored-procedure-best-practices-checklist.aspx

    --Prashanth

    • Proposed as answer by Eric__Zhang Monday, January 12, 2015 5:28 AM
    • Marked as answer by Charlie Liao Tuesday, January 13, 2015 9:45 AM
    Tuesday, January 6, 2015 3:39 PM
  • Best practices
    • capitalize reserved words
    • main keywords on new line
    • can't get used to commas before columns
    • always use short meaningful table aliases
    • prefix views with v
    • prefix stored procs with sp (however don't use "sp_" which is reserved for built in procs)
    • don't prefix tables
    • table names singular
    • Use the brackets around objects, so the query engine excplicitly knows a field when it sees it
    • Use THE SAME CASE as table object names and field names
    • When calling SPs from application, use the fully qualified [dbo].[procName] with correct owner AND case. Not Kidding! Read the articles above!
    • Reference the owner of the object so security is explicitly known and doesn't have to be figured out
    • Use SET NOCOUNT ON and SET NOCOUNT OFF to eliminate the extra overhead to keep track of how many records are updated in the stored proc unless you need them. Normally, you don't and you can gain a huge increase in performance.

    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    • Marked as answer by Charlie Liao Tuesday, January 13, 2015 9:45 AM
    Wednesday, January 7, 2015 1:01 PM
  •  

    Hi ITBobbyP,

                        As you know after these discussion points that there is no such very standardized format to write it but you can do some good DOs while you writing Stored Procedures. Some good points mentioned by Ahsan. Here are some more, which I follow..... Hope this will help you... :)

                                                    Programming point of view

    1. Use of SETNOCOUNT ON;

    2. USE TRY..CATCH block.

    3. Use of proper error msg to return error msg and procedure @@ERROR_MSG or @@ERROR_PROCEDURE.(These are not exact same but you can search on this basis).

    4. Write Small Transaction instead big one.

    5. Datatype of variable used should match with filtering column's Datatype.

    6. Use BEGIN...END.

    7. IF your procedure is called many times for different set of data again and again then use Recompilation Option.

                                                   Formatting Point of View

    1. Always use proper standard white spacing while writing your code.

    2. If you write nested transaction or nested TRY blocks then use proper white space.

    3. While writing CASE statement use BRAKETING for I identify nested begging and ending of statement.

    4. Use proper commenting for identifies the specific set of code.

    5. In the beginning of the stored procedure write comment with information about Who as created the procedure, Date of creation, Purpose of SP and Alter History if any.

     

    ---------------------------------------------------------------

    If you find it helpful then kindly vote. :)


    Shivendra

    • Marked as answer by Charlie Liao Tuesday, January 13, 2015 9:45 AM
    Wednesday, January 7, 2015 2:07 PM

All replies

  • Links for your reference

    http://blogs.msdn.com/b/cellfish/archive/2009/08/12/20-tips-to-write-a-good-stored-procedure-is-really-just-12.aspx

    http://sqlblog.com/blogs/aaron_bertrand/archive/2008/10/30/my-stored-procedure-best-practices-checklist.aspx

    --Prashanth

    • Proposed as answer by Eric__Zhang Monday, January 12, 2015 5:28 AM
    • Marked as answer by Charlie Liao Tuesday, January 13, 2015 9:45 AM
    Tuesday, January 6, 2015 3:39 PM
  • One helpful tip if you are using SQL 2012:

    1. Right click on Query designer window and click Insert Snippet
    2. Type Stored Procedure and select Basic Template 
    3. You get SP template and you can edit SP Name and parameter

    Few more best practices in below:

    http://www.c-sharpcorner.com/UploadFile/skumaar_mca/good-practices-to-write-the-stored-procedures-in-sql-server/


    -Vaibhav Chaudhari


    Wednesday, January 7, 2015 5:26 AM
  • Semicolon(;) is not mandatory while writing queries on stored procedure,Even without semicolon also the query will work. but as per the industry Standard you should use semicolon for each and every statement..........

    Thanks & Regards RAJENDRAN M

    Wednesday, January 7, 2015 5:33 AM
  • SoftwareHigh  engineering is still the same. High cohesion and low coupling, ISO-11179 naming rules, etc. Getaq copy of my SQL STYLE book for details. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Wednesday, January 7, 2015 5:57 AM
  • There is no such standard for stored procedure defined.

    You can find so many resources on best practices for writing stored procedure . 

    To start with you can refer stored procedure created under sample Database AdventureWorks2012.

    You can download it from codeplex site.

     

    Sandip Pani http://SQLCommitted.com

    Wednesday, January 7, 2015 5:58 AM
  • Best practices
    • capitalize reserved words
    • main keywords on new line
    • can't get used to commas before columns
    • always use short meaningful table aliases
    • prefix views with v
    • prefix stored procs with sp (however don't use "sp_" which is reserved for built in procs)
    • don't prefix tables
    • table names singular
    • Use the brackets around objects, so the query engine excplicitly knows a field when it sees it
    • Use THE SAME CASE as table object names and field names
    • When calling SPs from application, use the fully qualified [dbo].[procName] with correct owner AND case. Not Kidding! Read the articles above!
    • Reference the owner of the object so security is explicitly known and doesn't have to be figured out
    • Use SET NOCOUNT ON and SET NOCOUNT OFF to eliminate the extra overhead to keep track of how many records are updated in the stored proc unless you need them. Normally, you don't and you can gain a huge increase in performance.

    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    • Marked as answer by Charlie Liao Tuesday, January 13, 2015 9:45 AM
    Wednesday, January 7, 2015 1:01 PM
  • Semicolon(;) is not mandatory while writing queries on stored procedure,Even without semicolon also the query will work. but as per the industry Standard you should use semicolon for each and every statement..........

    Using semicolon statement terminators is not just an ANSI standard, but a SQL Server one.  Microsoft announced with the SQL Server 2008 release that semicolon statement terminators will become mandatory in a future version, albeit probably not in my lifetime :-) 

    http://www.dbdelta.com/always-use-semicolon-statement-terminators/


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Wednesday, January 7, 2015 1:43 PM
  •  

    Hi ITBobbyP,

                        As you know after these discussion points that there is no such very standardized format to write it but you can do some good DOs while you writing Stored Procedures. Some good points mentioned by Ahsan. Here are some more, which I follow..... Hope this will help you... :)

                                                    Programming point of view

    1. Use of SETNOCOUNT ON;

    2. USE TRY..CATCH block.

    3. Use of proper error msg to return error msg and procedure @@ERROR_MSG or @@ERROR_PROCEDURE.(These are not exact same but you can search on this basis).

    4. Write Small Transaction instead big one.

    5. Datatype of variable used should match with filtering column's Datatype.

    6. Use BEGIN...END.

    7. IF your procedure is called many times for different set of data again and again then use Recompilation Option.

                                                   Formatting Point of View

    1. Always use proper standard white spacing while writing your code.

    2. If you write nested transaction or nested TRY blocks then use proper white space.

    3. While writing CASE statement use BRAKETING for I identify nested begging and ending of statement.

    4. Use proper commenting for identifies the specific set of code.

    5. In the beginning of the stored procedure write comment with information about Who as created the procedure, Date of creation, Purpose of SP and Alter History if any.

     

    ---------------------------------------------------------------

    If you find it helpful then kindly vote. :)


    Shivendra

    • Marked as answer by Charlie Liao Tuesday, January 13, 2015 9:45 AM
    Wednesday, January 7, 2015 2:07 PM
  • Plenty of good ideas out there - but just a few add-on thoughts:

    There is a wide variety of conventions used out in the field (over 18 years I've never seen two organizations use the same exact set of standards). Even for those clients with well-defined coding conventions, there are always exceptions - especially when you add third party applications to the mix.

    While there are many conflicting standards out there – the most important thing to me is consistency.  Once a standard is chosen, I like to see it applied consistently.  The primary benefit of this is supportability, maintainability and overall comprehension.  If I’m reading code at 2AM due to an urgent issue – if everyone is using a consistent style, we can focus on fixing the issue instead of encountering speed bumps due to stylistic changes.  Pick a set of conventions that makes sense to you and your team, and then make sure it is applied consistently.


    Best Regards, Joe Sack, MVP, SQL MCM | SackHQ.com

    Wednesday, January 7, 2015 2:29 PM
  •  

    Hi Joseph :),

                     Good to see you.. For this comment as well as I have started my DB career with your "Sql Recipe" book...:)..A special thanks to you for it...:):)

                     Given point is very practical... We try to make it constantly, as it increases some troubleshooting time but it will benefit the next developer to think off next time...


    Shivendra

    Wednesday, January 7, 2015 2:45 PM