Set statement options
After you create or modify a stored procedure, the database engine will save set quoted_identifier and set
Ansi_nulls settings. These original settings are used when the stored procedure is executed. Therefore, when executing a stored procedure, the set quoted_identifier of any client session is ignored.
And set ansi_nulls. Set quoted_identifier and set ansi_nulls
The statement does not affect the stored procedure function.
Other set options (such as set arithabort, set ansi_warnings, or set
Ansi_paddings) is not saved after the stored procedure is created or changed. If the Stored Procedure logic depends on specific settings, you should add a set statement at the beginning of the process to ensure that the settings are correct.
When the set statement is executed from the stored procedure, this setting is only valid before the stored procedure is completed. Then, the setting is restored to its value when calling the stored procedure.
This allows individual clients to set the required options without affecting the Stored Procedure logic.
Note: |
Ansi_warnings is ignored when parameters in stored procedures or user-defined functions are passed, or when variables in batches are declared and set. For example, if you define a variable Char (3), and then set its value to greater than three characters, the data will be truncated to the defined size, the insert or update statement will be successful. |