Can you SET ANSI_NULLS in dynamic sql?

• Chad Michael

  SSC-Addicted

  Points: 427

  More actions

  January 10, 2006 at 4:45 pm

  #89106

  I have a stored procedure that can persist queries it generates to either stored procs or multi-statement table valued function. It's working fine except I have been unable to get the procs and functions I create by the dynamic sql to accept ANSI_NULLS ON. It seems that you can do it with QUOTED_IDENTIFIER ON just by doing this right before the dynamic sql to create the object:

  EXEC sp_executesql N'SET QUOTED_IDENTIFIER ON'

  But ANSI_NULLS doesn't "stick" using this same technique. Is there another way to do it? I care about this because I NEED ANSI_NULLS on as these functions are referencing indexed views.

  Thanks,

  -Chad

• Chad Michael

  SSC-Addicted

  Points: 427

  More actions

  January 10, 2006 at 5:45 pm

  #613959

  Ahh, figured it out. You can do it by nesting the EXECs

  EXEC ('SET ANSI_NULLS ON EXEC(''' + @sql + ''')')

  cool.