You can use a SET statement inside a stored procedure. The SET option you choose remains in effect during the execution of the stored procedure and then reverts to its former setting.
Inside a stored procedure, object names used with certain statements (the utility statements) must be qualified with the object owner's name if other users are to use the stored procedure. The utility statements are:
If you use SELECT * in a CREATE PROCEDURE statement, the procedure (even with the WITH RECOMPILE option to EXECUTE) does not recognize any new columns you might have added to the table after the procedure was created. To include new columns, you must drop the procedure and then re-create it.