SQL Coding Standards


Best SQL Coding Style with examples

1. Keywords must be Capitalized.
2. Database Object names & column names must be typed as it is in the database.
3. After comma need to put single space.
4. Need to put a single space before and after each operators (like +, -, *, /, %, AND, OR etc).

SELECT
    StudentId, StudentName, Age,
FROM StudentMaster
    WHERE IsActive = 0

5. If Select/Insert clause contains more columns, need to split column names 5 number per line.

INSERT INTO StudentMaster
    (
        StudentId, StudentName, Age, Gender, State,
        IsActive
    )
    VALUES
    (
        100023, ‘Sandeep’ , 26, 'Male', 'Kerala',
        True
    )

6. Need to include If Exists checking & DROP command before typing the CREATE command.

IF OBJECT_ID('tempdb..#temptable') IS NOT NULL
    DROP TABLE #temptable

7.  After the ‘AS’ command need to place the Creation & Modification Description Section.

CREATE PROCEDURE GetStudentDetails
    AS
    /***************************************************
        Created By        :    XXXX
        Created On        :    dd/mm/yyyy
        Purpose              :   XXXXXX
        Product              :    Test
        Description        :    special notes for this SPs
        Testing        :    EXEC GetStudentDetails @StudentId = 1
    --------------------------------------------------------------------------------------------------------
        Modified By          Modified On     Version      Reason
     --------------------------------------------------------------------------------------------------------
        xxxxxx x             dd/mm/yyyy        1.1.5.0       New Parameter added
       <New Entry>
     --------------------------------------------------------------------------------------------------------

    ***************************************************/

8. Need to add informative comments after the ‘--// ‘ symbol.

--//  For Looping Upto the Count
   WHILE(@Count<= @TotalCount)
    BEGIN
    …
    …

9. Each Parameter should be placed in different line after a single Tab.
10. Parameter DataType should be specified after the Parameter name by putting a single space.

CREATE PROCEDURE GetStudentDetails
            @param1 DATATYPE,
            @param2 DATATYPE,
            @param3 DATATYPE
        AS
        …

11. Keep the alignment


No comments:

Post a comment