A SQL stored procedure is a set of SQL statements with an assigned name that is stored in the database in compiled form so that it can be shared by a number of programs. An example SQL stored procedure can be created with the following SQL statements:
CREATE PROCEDURE SP_deleteTestTable () BEGIN DELETE FROM test; END;
After the stored procedure is created, its definition can be retrieved by executing a show procedure query:
SHOW PROCEDURE SP_deleteTestTable;
Copying SQL Stored Procedures Using Data Mover
Data Mover copies SQL stored procedures as follows:
- Data Mover retrieves the SQL stored procedure creation DDL by executing a show procedure dbname.spname SQL statement.
- Data Mover does not parse a create procedure DDL; it executes the exact same DDL on the target system. It does not relocate or rename stored procedure objects.
- If the procedure name in the procedure definition is not fully qualified, the procedure is created in the database having the same name as the source procedure database.
- If the table names in the procedure definition are not fully qualified, those tables must exist in the database having the same name as the source procedure database to avoid an error.
- If the object being copied does not exist on the target database, it is created on the target database. If the object does exist on the target database, the object is overwritten – unless the overwrite_existing_objects property is set to false. In that case, a create time error is generated.
- If a procedure is part of a full database copy, the procedure is copied with the database using DSA. It is not copied separately.