Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Unanswered: Concat_null_yields_null

    How do you set the option CONCAT_NULL_YIELDS_NULL to OFF
    in all databases and permenantly

    ???

  2. #2
    Join Date
    Jan 2003
    Location
    India
    Posts
    523

    Re: Concat_null_yields_null

    Originally posted by Karolyn
    How do you set the option CONCAT_NULL_YIELDS_NULL to OFF
    in all databases and permenantly

    ???
    check sp_dboption in bol

  3. #3
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: Concat_null_yields_null

    USE master
    EXEC sp_dboption 'M158005', 'concat null yields null', 'FALSE'

  4. #4
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: Concat_null_yields_null

    USE master
    EXEC sp_dboption 'M158005', 'concat null yields null', 'FALSE'

    this command doesn't change anything

    Select NULL + 'TOTO'
    ----> NULL

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm replying because Karolyn asked me to in another thread

    "How do you set the option CONCAT_NULL_YIELDS_NULL to OFF
    in all databases and permenantly"

    i have no idea




    whenever i run into null issues with concatenation, i always use COALESCE

    e.g.

    select coalesce(foo,'') & coalesce(bar,'') as foobar
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I agree with Rudy. I don't think code should rely on this setting being one value or another.
    If it's not practically useful, then it's practically useless.

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

  7. #7
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    The pb is that i'm migrating sql queries written for SYBASE
    and I've got to check for concatenations in all queries
    to put COALESCE() or ISNULL()
    on each columns that can have a NULL value
    ...

    I've finished migrating the queries but I still have to
    check in the stored proc.

    and there's lots of other programs to migrate

    so setting this option should facilitate our migration

Posting Permissions

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