I know this post is ancient, but well-formatted code never goes out of style.
I use this template for all my procedures. Some people do not like the detailed code and comments, but as a person who often has to update stored procedures that have not been touched on since the mid-90s, I can tell you the value of writing well-formatted and carefully commented code. Many were written to be as concise as possible, and sometimes it may take several days to understand the meaning of the procedure. It's pretty easy to see what a block of code does by simply reading it, but it’s much harder (and sometimes impossible) to understand the meaning of the code without proper commenting.
Explain it as if you are going through a junior developer. Suppose that the person reading it knows almost nothing about the functional area to which it refers, and has a limited understanding of SQL. What for? Many times, people have to look at the procedures to understand them, even if they do not intend or the business changes them.
/*************************************************************************************************** Procedure: dbo.usp_DoSomeStuff Create Date: 2018-01-25 Author: Joe Expert Description: Verbose description of what the query does goes here. Be specific and don't be afraid to say too much. More is better, than less, every single time. Think about "what, when, where, how and why" when authoring a description. Call by: [schema.usp_ProcThatCallsThis] [Application Name] [Job] [PLC/Interface] Affected table(s): [schema.TableModifiedByProc1] [schema.TableModifiedByProc2] Used By: Functional Area this is use in, for example, Payroll, Accounting, Finance Parameter(s): @param1 - description and usage @param2 - description and usage Usage: EXEC dbo.usp_DoSomeStuff @param1 = 1, @param2 = 3, @param3 = 2 Additional notes or caveats about this object, like where is can and cannot be run, or gotchas to watch for when using it. **************************************************************************************************** SUMMARY OF CHANGES Date(yyyy-mm-dd) Author Comments ------------------- ------------------- ------------------------------------------------------------ 2012-04-27 John Usdaworkhur Move Z <-> X was done in a single step. Warehouse does not allow this. Converted to two step process. Z <-> 7 <-> X 1) move class Z to class 7 2) move class 7 to class X 2018-03-22 Maan Widaplan General formatting and added header information. 2018-03-22 Maan Widaplan Added logic to automatically Move G <-> H after 12 months. ***************************************************************************************************/
In addition to this header, your code should be well-commented and outlined from top to bottom. Add comment blocks to the main functional sections, such as:
/*********************************** ** Process all new Inventory records ** Verify quantities and mark as ** available to ship. ************************************/
Add a lot of inline comments explaining all but the most basic criteria, and ALWAYS format your code for easy reading. Long vertical pages of indented code are better than wide short pages and make it much easier to see where blocks of code begin and end years later when someone else supports your code. Sometimes wide, indented code is more readable. If so, use it, but only when necessary.
UPDATE Pallets SET class_code = 'X' WHERE AND class_code != 'D' AND class_code = 'Z' AND historical = 'N' AND quantity > 0 AND GETDATE() > DATEADD(minute, 30, creation_date) AND pallet_id IN (