MySQL Stored Procedure – Call from One DB to Get Result in Another

MySQLmysql-5.6mysql-workbenchstored-procedures

CREATE DEFINER=root@localhost
PROCEDURE AccountReqs( IN IsNewAccountStart VARCHAR (255)
                 , In AccountName varchar (255)
                 , IN AccountNumber varchar(255)
                 , In PaymentTerm varchar(255)
                 , IN Batch varchar (255)
                 , In AdditionalEmailAddresses varchar (500)
                 , In WorkEmail varchar(500)
                 , In PersonalEmail varchar (500) )`
BEGIN
    SELECT * FROM ACCOUNTS
    WHERE IsNewAccountStart <>'TRUE' AND IsNewAccountStart <>' ' 
    Union all 
    SELECT * FROM ACCOUNTS WHERE AccountName=' ' 
    Union all 
    SELECT * FROM ACCOUNTS WHERE AccountNumber=' ' 
    Union all 
    SELECT * FROM ACCOUNTS 
    WHERE PaymentTerm <> 'Net 30' 
    AND PaymentTerm <> 'Net 45' 
    AND PaymentTerm <> 'Net 60' 
    AND PaymentTerm<> 'Due Upon Receipt' 
    Union All 
    SELECT * FROM ACCOUNTS WHERE Batch <> 'Batch1';

    SELECT * FROM ACCOUNTS
    WHERE AdditionalEmailAddresses <> ' '
    OR WorkEmail <> ' '
    OR PersonalEmail <> ' ';

END

Best Answer

SUGGESTION #1

You need to explicitly set the database before calling the Stored Procedure

USE targetDB
CALL Shared.AccountReqs(...);

SUGGESTION #2 (OPTIONAL)

Since you have all these UNION clauses against the same table, try combining all the WHERE clauses under a single SELECT. I see two SELECTs in your original question. Therefore, the Stored Procedure should look more like this:

CREATE DEFINER=`root`@`localhost` PROCEDURE `AccountReqs`(
  IN IsNewAccountStart VARCHAR (255),
  In AccountName varchar (255),
  IN AccountNumber varchar(255),
  In PaymentTerm varchar(255),
  IN Batch varchar (255),
  In AdditionalEmailAddresses varchar (500),
  In WorkEmail varchar(500),
  In PersonalEmail varchar (500)
)
BEGIN
    SELECT * FROM ACCOUNTS 
    WHERE (IsNewAccountStart <>'TRUE' AND IsNewAccountStart <>' ')
    OR    (AccountName=' ') OR (AccountNumber=' ')
    OR    (PaymentTerm NOT IN ('Net 30','Net 45','Net 60','Due Upon Receipt'))
    OR    Batch<> 'Batch1';

    SELECT * FROM ACCOUNTS
    where AdditionalEmailAddresses<>' '
    Or  WorkEmail<> ' '
    Or  PersonalEmail<> ' ';
END
Related Question