Skip to main content
Version: 2023

SQL formatting guidelines

Readable code helps you as well as your colleagues. It makes it easier to understand, transfer, and share your work.

You can deliver readable code by sharing formatting guidelines as a team. Adhere to your shared formatting guidelines to make your team's code more consistent.

On this page, you can find a basic set of SQL formatting guidelines that can serve as a starting point for your team. These guidelines also provide examples of what your properly formatted code might look like.

Basics

Your code should be readable and easy to understand. Other people should be able to grasp what your code is doing by reading your comments. The structure of your code should be easy to follow.

Do's

  • Explain each block of code using comments.
  • Put a comment after each domain or literal that you use.
  • Indent code blocks.
  • Use spaces, not tabs, for indentation.
  • Use commas in front of column names.
  • Use begin/end after stating a condition.
  • Use column lists for inserts.
  • Align opening and closing brackets on the same column.
  • Include aliases for calculated values or literals.
  • Use lower case only.

Don'ts

  • Use reserved keywords.
  • Leave commented development items in your code, like variable assignments or helper queries.

Examples

Basic alignment

select si.sales_invoice_id
,si.invoice_date
,si.due_date
from sales_invoice si
where sales_invoice_status = 'o' --open

Alias alignment and use of functions

select si.sales_invoice_id
,si.due_date
,getdate() as today
,datediff(day
,si.invoice_date
,si.due_date
) as number_of_days
from sales_invoice si

Join alignment

select si.sales_invoice_id
,si.due_date
,sil.amount
,so.sales_order_number
from sales_invoice si
join sales_invoice_line sil
on sil.invoice_id = si.invoice_id
left join sales_order so
on so.sales_order_id = sil.sales_order_id

If statement

--Notify the user when the amount open on the invoice is greater than 0 and the due date lies in the past. 
if exists (select *
from invoice i
where i.invoice_id = @invoice_id
and i.amount_open > 0
and i.due_date >= getutcdate()
)
begin
exec tsf_send_message amount_open_greater_than_zero, null, 0
end

Case statement: simple case

select case si.sales_type
when 1 then 1 --1 = internal, 1 = partner
when 2 then 3 --2 = external, 3 = customer
else 0 --0 = other
end as sales_category
from sales_invoice si

Case statement: search case

select case when sol.sales_price > sol.max_price --when sales price is greater than the max price. 
then 1
else 0
end as exceeds_max_price
from sales_order_line sol

More complex select query

with wolz (work_order_line_id
,branch_subsidiary
)
as (select wol.work_order_line_id as work_order_line_id
,wol.amount_of_items * processing_time as time_required
from work_order_line wol
)
select wol.call_description as call_description
from work_order_line wol
join wolz w
on w.work_order_line_id = wol.work_order_line_id
where wol.work_order_category_id = @work_order_category_id
and (select sum(h.number_of_hours) as number_of_hours
from hour h
where h.project_id = p.project_id
) <= 100
group by wol.subsidiary_id
,w.time_required
having sum(wol.work_order_id) = 1
and count(*) = 3
order by wol.branch_id
,w.time_required

Comparison alignment

select wo.subsidiary_id as subsidiary_id
,wo.branch_id as branch_id
,wo.work_order_id as work_order_id
from work_order wo
join work_order_line wol
on wol.work_order_id = wo.work_order_id
and wol.subsidiary_id = wo.subsidiary_id
and wol.branch_id = wo.branch_id
join work_order_line_part wolp
on wolp.subsidiary_id = wol.subsidiary_id
and wolp.branch_id = wol.branch_id
and wolp.work_order_id = wol.work_order_id
and wolp.work_order_line_id = wol.work_order_line_id
where wo.work_order_category_id = @work_order_category_id
and wol.amount >= 40
and ( wol.clearance = 1 --true
or wo.customer_reference = 4 --ref_x
)

Variables

declare @contact_person_id               person_id
,@contact_organisation_id organisation_id
,@is_default_contact_person bit = 0 --default false
,@is_default_contact_organisation bit = 0 --default false

select @contact_person_id = o.person_id
,@contact_organisation_id = o.organisation_id
from organisation o

if @contact_person_id is not null
begin
set @is_default_contact_person_id = 1
set @is_default_contact_organisation_id = 1
end

Insert based on variables

insert into task(person_id
,dt.default_task_id
,task_name
,task_description
,added_by
,added_on
)
values (@person_id
,@default_task_id
,@task_name
,@task_description
,dbo.tsf_user()
,getutcdate()
)

Insert based on select statement

insert into task(person_id
,default_task_id
,task_name
,task_description
,added_by
,added_on
)
select p.person_id
,dt.default_task_id
,dt.task_name
,dt.task_description
,dbo.tsf_user()
,getutcdate()
from person p
join person_default_task pdt
on pdt.person_id = p.person_id
join default_task dt
on dt.default_task_id = pdt.default_task_id
where p.person_id = @person_id
and dt.active = 1 --only active default tasks.

Merge statement

merge into task trgt
using (select p.person_id
,dt.default_task_id
,dt.task_name
,dt.task_description
,dbo.tsf_user() as added_modified_by
,getutcdate() as added_modified_on
from person p
join person_default_task pdt
on pdt.person_id = p.person_id
join default_task dt
on dt.default_task_id = pdt.default_task_id
where p.person_id = @person_id
and dt.active = 1 --only active default tasks.
)src
on ( src.person_id = trgt.person_id
and src.default_task_id = trgt.default_task_id
)
when matched
then update
set task_name = src.task_name
,task_desciption = src.task_description
,modified_by = added_modified_by
,modified_on = added_modified_on
when not matched
then insert (person_id
,default_task_id
,task_name
,task_description
,added_by
,added_on
)
values(src.person_id
,src.default_task_id
,src.task_name
,src.task_description
,src.added_modified_by
,src.added_modified_on
);

Was this page helpful?

Happy React is loading...