2
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
1

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
  • CALL Shared.AccountReqs(...); i do not understand what would i be passing here in parentheses . I need this query to give me the result based on the data that is already in the target Database – Elliina Sep 15 '15 at 15:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.