Object Identifiers

Consideration for working with object identifiers.

  • To change the name of a stored procedure, use sp_rename.

  • To change the name of an extended stored procedure, drop the procedure, rename and recompile the supporting function, then re-create the procedure.

  • If a procedure references table names, column names, or view names that are not valid identifiers, you must set quoted_identifier on before the create procedure command and enclose each such name in double quotes. The quoted_identifier option does not need to be on when you execute the procedure.

  • You can replace the procedure if any of the objects it references have been renamed..

  • Inside a stored procedure, object names used with the create table and dbcc commands must be qualified with the object owner’s name if other users are to make use of the stored procedure. For example, user “mary,” who owns the table marytab, should qualify the name of her table inside a stored procedure (when it is used with these commands) if she wants other users to be able to execute it. This is because the object names are resolved when the procedure is run. When another user tries to execute the procedure, the SAP ASE server looks for a table called marytab owned by the user “mary” and not a table called marytab owned by the user executing the stored procedure.

    Thus, if marytab is not qualified, and user “john” tries to execute the procedure, the SAP ASE server looks for a table called marytab owned by the owner of the procedure (“mary,” in this case) or by the database owner if the user table does not exist. For example, if the table mary.marytab is dropped, the procedure references dbo.marytab.

    Object names used with other statements (for example, select or insert) inside a stored procedure need not be qualified because the names are resolved when the procedure is compiled.