Error Code 1046: No database Selected

Question

I wrote a stored procedure (sp_archivev3) on MySQl Workbench which is as follows. Basically, Inserting values from one database to another.

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`MailMe`@`%` PROCEDURE `sp_archivev3`()
BEGIN

INSERT INTO 
     send.sgev3_archive(a_bi,
                        b_vc,
                        c_int,
                        d_int,
                        e_vc,
                        f_vc,
                        g_vc,
                        h_vc,
                        i_dt,
                        j_vc,
                        k_vc,
                        l_vc,
                        m_dt,
                        n_vch,
                        o_bit)
SELECT     a_bi,
           b_vc,
           c_int,
           d_int,
           e_vc,
           f_vc,
           g_vc,
           h_vc,
           i_dt,
           j_vc,
           k_vc,
           l_vc,
           m_dt,
           n_vch,
           o_bit

FROM   send.sgev3

WHERE m_dt BETWEEN  '2014-06-09' AND CURDATE();


END

When I run call sp_archivev3(); , I get an error with an error code 1046: No database selected SELECT the default DB to be used by double-clicking its name in the SCHEMAS list in the sidebar.

Please let me know what's wrong with my stored procedure.

Answer 1

The problem is that MySQL doesn't know which procedure named sp_archivev3 is supposed to be executed; MySQL doesn't know which database to look in. (Stored programs are objects in a specific database, just like tables are objects in a specific database.)

Either specify the current database with USE statement:

use mydatabase;
call sp_archivev3();

or qualify the procedure with the name of database:

call mydatabase.sp_archivev3();