SQL-92 Settings for Stored Procs | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

SQL-92 Settings for Stored Procs

I am looking for some information on where to set the SQL-92 Settings Statements and why. I have seen the statements, such as "SET ANSI_DEFAULTS ON" or "SET ANSI_NULLS ON", set before the drop of a procedure, before the create procedure command and inside the proc itself. It seems the proc behaves differently depending on where these settings are made. I have not been able to find the final word from Microsoft on this. I know a number of these settings cause the proc to recompile but does it matter if it is outside or inside the proc? If the settings are before the create procedure command then how does the proc know to follow these settings when the proc is called from an app? Thanks

See if this helps:
Along with the explanations you can find in BOL.