Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Jun 2007
    Location
    Ohio, USA
    Posts
    142

    Unanswered: Odd coding practice...?

    I've been seeing this a lot lately, and can't fathom why:

    Code:
     
    -- The table...
    CREATE TABLE TableName (
      ColumnA char(2) NULL,  -- We know what goes here
      ColumnB char(2) NULL,  -- And what goes here.
      ColumnC char(2) NULL,  -- This will get populated later.
      ColumnD char(2) NULL   -- This also gets populated later.
    )
     
    INSERT INTO TableName (
      ColumnA,
      ColumnB,
      ColumnC,
      ColumnD
      )
    SELECT 
      Col1, 
      Col2, 
      NULL, 
      NULL
    FROM SourceTable
    My question is, why bother selecting nulls? Couldn't the same be accomplished with:

    Code:
    INSERT INTO TableName (ColumnA, ColumnB)
    SELECT Col1, Col2
    FROM SourceTable
    ... with much less code? Is there a reason to select nulls and I'm just missing something?

    Thanks.
    -D.
    David Maxwell
    Data Integrity? Yeah, I've heard of that...

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes, it is syntactically unnecessary.
    But from the standpoint of coding rigor, it might be the developer's way of ensuring that all his columns are accounted for, and declaring that the omission of data was intentional.
    I would be hesitant to fault a coder for being overly specific.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    There is a difference. If the columns have defaults then the default value will be used if you don't reference the column in the INSERT column list.

    If you specify nulls then nulls will be inserted whatever the default (assuming nulls are allowed).

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    His sample code had no such defaults...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    True, but people tend to leave things out when they post DDL. This one looks like an over-simplified example (no keys, meaningless names) so maybe in reality there are defaults or maybe the original developer was coding defensively against the possibility of defaults being added in future. Not that I would really advocate using NULLs like that but just thinking about a possible explanation.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    readysetstop is one of my coworkers. If he is posting sloppy incomplete code, I'll have his butt fired.
    Just kidding, David....missed you at the quarterly.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Wow. Talk about a rough neighborhood!

    -PatP

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by ReadySetStop
    I've been seeing this a lot lately
    Quote Originally Posted by blindman
    I would be hesitant to fault a coder for being overly specific.
    Quote Originally Posted by blindman
    readysetstop is one of my coworkers.
    Sounds to me that RedySetStop has been given the task of reviewing blindman's code.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    That had actually crossed my mind...
    ...but I don't think he's working on any of my previous projects.

    I'm going to keep on eye on you from now on, Sherlock Pootle.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I'm hurt that you are only starting now....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Jun 2007
    Location
    Ohio, USA
    Posts
    142
    Quote Originally Posted by blindman
    readysetstop is one of my coworkers. If he is posting sloppy incomplete code, I'll have his butt fired.
    Just kidding, David....missed you at the quarterly.
    *Ahem*... My code was in no way incomplete, thank you... If the fields had defaults, then I would know by context clues that the nulls were deliberate.

    Missed you too, Bruce. I was still a bit ill at the quarterly, so I tried not to stick around too long. We'll talk more in the near future.

    -D.
    David Maxwell
    Data Integrity? Yeah, I've heard of that...

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by ReadySetStop
    Missed you too, Bruce.
    Dammit. I had people here guessing at my first name. Thanks a lot.
    Please, don't spill the beans that I am not really blind.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by blindman
    Please, don't spill the beans that I am not really blind.
    You mean in the physical sense, right?

    -PatP

  14. #14
    Join Date
    Jun 2007
    Location
    Ohio, USA
    Posts
    142
    Quote Originally Posted by blindman
    Dammit. I had people here guessing at my first name. Thanks a lot.
    Please, don't spill the beans that I am not really blind.
    You're not? What's with the dark glasses and the cane, then?

    (Sorry about the name-thing. )
    David Maxwell
    Data Integrity? Yeah, I've heard of that...

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by ReadySetStop
    (Sorry about the name-thing. )
    Ah don't worry about it - we cracked the code ages ago. Although I did have you down as a Terry.

    You know you could have achieved the same as here simply by popping round to blindman's cube but this way is much more efficient and effective. Isn't the internet brilliant!
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •