CODEWITHSUNDEEP
sqlsql-servert-sql
Agnius Vasiliauskas
Agnius Vasiliauskas

Reputation: 11277

Documenting constants in query

We all know that using magic numbers in code is a bad practice. So what's a best way to give a meaning to some constant number in query ? Usual way would be to just write a comment like so:

select * from
(
    select n, n % 2 rem from
    (
        select 1 n
        union all
        select 2
        union all
        select 3
        union all
        select 4
    ) tmp
) tmp2
where tmp2.rem = 0 /* Only Even Numbers */

But we can also do like that:

declare @onlyEvenNumbers int 
set @onlyEvenNumbers = 0

select * from
(
    select n, n % 2 rem from
    (
        select 1 n
        union all
        select 2
        union all
        select 3
        union all
        select 4
    ) tmp
) tmp2
where tmp2.rem = @onlyEvenNumbers

Or simply:

select * from
(
    select n, n % 2 rem from
    (
        select 1 n
        union all
        select 2
        union all
        select 3
        union all
        select 4
    ) tmp
) tmp2
where tmp2.rem = (select 0 as onlyEvenNumbers)

Given that sometimes queries can be very long and one line can consist of many constant numbers - What's a best way to document these magic numbers so that usual developer could understand query faster ?

Upvotes: 0

Views: 23

Answers (1)

knagaev
knagaev

Reputation: 2957

May be better?

select * from
(
    select n, n % 2 DivBy2Remainder from
    (
        select 1 n
        union all
        select 2
        union all
        select 3
        union all
        select 4
    ) tmp
) tmp2
where tmp2.DivBy2Remainder = 0

Upvotes: 1

Related Questions