Set Options in Stored Procedures

You can use almost all of the set command options inside stored procedures. The set option remains in effect during the execution of the procedure, and most options revert to the former setting at the close of the procedure.

Only the dateformat, datefirst, language, and role options do not revert to their former settings.

However, if you use a set option (such as identity_insert) that requires the user to be the object owner, a user who is not the object owner cannot execute the stored procedure.

• Query Optimization Settings
  You can export optimization settings, such as set plan optgoal and set plan optcriteria, using set export_options on. Optimization settings are not local to the stored procedure; they apply to the entire user session.
• Maximum Number of Arguments
  The maximum number of arguments for stored procedures is 2048. However, you may notice a performance degradation if you execute procedures with large numbers of arguments, because the query processing engine must process all the arguments and copy argument values to and from memory.
• Maximum Size for Expressions, Variables, and Arguments
  The maximum size for expressions, variables, and arguments passed to stored procedures is 16384 bytes (16K), for any page size. This can be either character or binary data. You can insert variables and literals up to this maximum size into text columns without using the writetext command.