January 26, 2016 at 1:39 pm
If you were to pseudo-code the below statement (to aid you in understanding what it does in context of a large number of statements within the same procedure), how would you do this - by summarizing it to the comment on the first line, or something else?
When I try to summarize/pseudocode large SQL procedures, I usually get caught-up in trying to summarize how the joins and where conditions work; I'd appreciate some ideas on how to summarize T-SQL code from a high-level/abstract sense, similar to how a flow-chart makes a process easier to follow.
-- Insert into tblclass any classes that are in #classdetails but missing from tblclass
INSERT INTO tblclass(
name ,
subject_id ,
campus_id ,
classtype_id
)
SELECT
tblclasstype.name ,
tblsubject.subject_id ,
#classdetails.campus_id ,
tblclasstype.classtypeid
FROM
#classdetails
LEFT JOIN tblclasstype
ON #classdetails.classtypeid = tblclasstype.classtypeid
-- Subjects with this subject code, with rownum ordered by available first, latest subject_id first
OUTER APPLY (
SELECT subject_id ,
ROW_NUMBER( )OVER( PARTITION BY tblsubject.subjectcode ORDER BY CASE WHEN available = 1 THEN 0 ELSE 1 END, subject_id DESC )AS rownum
FROM tblsubject
WHERE tblsubject.subjectcode = #classdetails.subjectcode COLLATE database_default
) as tblsubject
-- Existing class with matching subject_id, classtype_id and campus_id
LEFT JOIN tblclass tblclass_sub
ON tblsubject.subject_id = tblclass_sub.subject_id
AND tblclass_sub.classtype_id = tblclasstype.classtypeid
AND tblclass_sub.campus_id = #classdetails.campus_id
WHERE
-- We only want the first subject entry (there should only be one active subject with the subject ID, if there are this will filter to the latest)
tblsubject.rownum = 1
-- No existing class exists
AND tblclass_sub.class_id IS NULL;
January 26, 2016 at 1:59 pm
Pseudo-code is for procedural programming, and should only be in the design document. Not in the comments. That's not what comments are for, they should clarify.
My commenting style is to first do a comment that describes the function of the query.
Then in the query itself I will add inline comments to explain the implementation choices and to help future me understand current me.
I do not like rules about how much comments to use. If something is obvious, I will not add a comment. If something needs a full novella to explain the logic, I will add a full novella in the comments (allthough I'd probably try to simplify it first).
February 21, 2016 at 12:39 pm
I believe comments should describe what a piece of code is setting out to do or trying to achieve at the high level.
Any other comments following that could be an explanation of possibly non-standard implementations.
Ultimately another coder will be looking at the code at some point and it shouldn't be necessary that he/she be handheld through what really is documented language constructs.
February 21, 2016 at 1:05 pm
Thanks - it's good to know heavily commenting reasonably standard code isn't recommended.
What about if you had a large (3000+ line) stored procedure from a previous developer that you needed to gain comprehension of - do you have any tips on how to break the procedure down to get a high-level overview of the logic when there isn't much help from comments or variables and table names? Would you flow-chart it? Try to write a sentence by sentence breakdown of what each statement does?
February 21, 2016 at 1:10 pm
Comments are most useful when they comminicate intent as opposed to structure. No need to rewrite Chapter 1 of every "how to write T-SQL" book alongside your code. However, you[\i] are writing this code and not everyone thinks like you so help the next person (which might be a future version of yourself who knows more than the current version of you) understand what you were thinking, what you observed or what you were told by an analyst or lead when the code was written.
As an aside, Hungarian notation for tables (and all objects for that matter) fell out of favor a long time ago. I hope this is not new development, and if it is that you still have a chance to shoft gears.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 21, 2016 at 1:37 pm
Thanks Orlando, the Hungarian notation is enforced by management, it's not by preference :-).
The situation I have is where a previous developer has left a system with large stored procedures with little documentation, and I need to break them down into something I can follow, at least as a way of understanding the current code so I can rewrite it into something more reasonable.
How would you go about documenting/abstracting/mapping an existing large stored procedure with the intention of trying to understand the overall picture and process?
February 22, 2016 at 6:42 am
Andrew P (2/21/2016)
Thanks Orlando, the Hungarian notation is enforced by management, it's not by preference :-).The situation I have is where a previous developer has left a system with large stored procedures with little documentation, and I need to break them down into something I can follow, at least as a way of understanding the current code so I can rewrite it into something more reasonable.
How would you go about documenting/abstracting/mapping an existing large stored procedure with the intention of trying to understand the overall picture and process?
I like to place a standard comment block at the top of every module, like this:
/********************************************************************************
Created: 2016-02-22
Purpose:
Author:
Example:
Modification History:
Date Author Purpose
----------- --------------- ----------------------------------------------------
********************************************************************************/
In the body of the module I like to add comments inline that convey the intent of whole sections, specific queries or even parts of queries
-- validate input parameters
-- apply updates to existing addresses
-- calculation results in 2nd Tuesday of the previous month
or to reduce the need to remember details about syntax, e.g. the style codes used with CONVERT.
-- 121 = yyyy-MM-dd HH:mm:ss.fff
CONVERT(VARCHAR(30), GETDATE(), 121)
When commenting code I like to think that if all the code were removed from the module that between the comment block at the top and the inline comments the reader could at minimum understand the intent of the module, if not some of the finer implementation details.
Inheriting code is sometimes difficult. Inheriting giant monoliths with no comments or documentation is even more difficult but, sadly, all too common. In general I am always looking for opportunities to refactor code into separate modules to make them easier to understand, easier to reuse and easier to write unit tests for. That approach usually serves me well when taking a look at a new piece of code because immediately the hardcoding will show up under that type of examination as will any proprietary logic that only applies in a small set of circumstances. Anything that shows itself that way is a good candidate for a comment.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 22, 2016 at 7:34 am
FWIW, even if the comments aren't overwhelming in the code posted, some might be prevented and the code can be improved to be almost self documenting.
-- Insert into tblclass any classes that are in #classdetails but missing from tblclass
INSERT INTO tblclass(
name ,
subject_id ,
campus_id ,
classtype_id
)
SELECT
tblclasstype.name ,
tblsubject.subject_id ,
#classdetails.campus_id ,
tblclasstype.classtypeid
FROM
#classdetails
LEFT JOIN tblclasstype
ON #classdetails.classtypeid = tblclasstype.classtypeid
OUTER APPLY (
SELECT subject_id ,
ROW_NUMBER( )OVER( PARTITION BY tblsubject.subjectcode ORDER BY CASE WHEN available = 1 THEN 0 ELSE 1 END, subject_id DESC )AS rownum
FROM tblsubject
WHERE tblsubject.subjectcode = #classdetails.subjectcode COLLATE database_default
) as tblsubject
WHERE
-- There should only be one active subject with the subject ID, ordered by available first, latest subject_id first
tblsubject.rownum = 1
-- No existing class exists
AND NOT EXISTS( SELECT 1 FROM tblclass tblclass_sub
ON tblsubject.subject_id = tblclass_sub.subject_id
AND tblclass_sub.classtype_id = tblclasstype.classtypeid
AND tblclass_sub.campus_id = #classdetails.campus_id);
Comments in a statement should explain business rules or complex sections. Basically to know what's going on without having to read the full statement.
Basically, I agree with what Orlando has posted.
February 23, 2016 at 6:10 am
Suggestion: Avoid using technical jargon if you're summarizing what a procedure is doing regardless of how you document it.
Instead of
-- Existing class with matching subject_id, classtype_id and campus_id
say something like:
Look in all existing classes that are the same type and subject on the same campus.
Not sure if that is exactly what your left join is doing, but you get the idea.
____________
Just my $0.02 from over here in the cheap seats of the peanut gallery - please adjust for inflation and/or your local currency.
February 24, 2016 at 8:17 pm
Thanks Orlando, that's exactly what I was after! Thank-you also for the great ideas Luis and Ishanahan, they will help with clarity going forward :-).
February 24, 2016 at 8:26 pm
You're welcome. Thanks for the feedback.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply