Post reply

SET ANSI NULLS OFF

  • April 4, 2011 at 10:54 am

    #248967

    Hi,

    Is there an automated way to SET ANSI NULLS OFF for a specific object type ?. For example, if i want to SET ANSI NULLS OFF for all the stored procedures in a database, is there a way to do this.

    Currently i have to run the script for every stored procedure.

    Thanks

  • April 4, 2011 at 11:12 am

    #1307081

    sankar.jayavelu (4/4/2011)

    Hi,

    Is there an automated way to SET ANSI NULLS OFF for a specific object type ?. For example, if i want to SET ANSI NULLS OFF for all the stored procedures in a database, is there a way to do this.

    Currently i have to run the script for every stored procedure.

    Thanks

    Since that particular setting (SET ANSI NULLS OFF) has been (IIRC) deprecated, my suggestion would be to learn how to properly use and compare NULLs instead of using the setting you propose.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • April 4, 2011 at 11:30 am

    #1307091

    Sure.. for future objects i would be checking it. But there a some old procedures and functions that currently uses the ANSI NULLS ON. Without changing the entire procedures and functions, i need to reset those.

  • April 4, 2011 at 8:20 pm

    #1307268

    There's no automated way unless you affect the entire session or database. You can add the setting to individual procs but that will affect the entire proc.

    Setting ANSI NULLS to OFF is still a bad idea IMHO.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply